Call webservice and insert/update sql table

Hi,

I have a basic transaction there I want to read the output from a webservice and put the result in a sql table and update this table after scheduled timebase?



//Mattias
semlunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
please provide more details.

as one of ideas, you may use WebService task in SSIS
more:
about Web Service Task
http://msdn.microsoft.com/en-us/library/ms140114(v=sql.105).aspx

and the  nice example:

Calling a Web Service from a SQL Integration Services package
http://www.codeproject.com/Articles/24999/Calling-a-Web-Service-from-a-SQL-Integration-Servi
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
SSIS is probably going to be the easiest way to set this up, as it's straight-forward to read from a webservice there.

Alternatively, you could set up a SQL-CLR assembly that reads from your webservice and outputs the results, and then you can insert those results directly into your table. Here's a simple example, and you'd need to expand it to use the data types you're returning:

http://blogs.msdn.com/b/spike/archive/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr.aspx

If you'd like more reading, here's another basic example to get you started:

http://blog.sqlauthority.com/2008/10/19/sql-server-introduction-to-clr-simple-example-of-clr-stored-procedure/

You can have the CLR assembly do anything you can do in normal .NET code, including call a web service and return the results. Not sure if you've got any .NET development experience, but if not, hopefully somebody at the office does and can help you (or you can post another question here with more specifics and we can do our best!).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eugene ZCommented:
semlun: does it mean you will go with CLR?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.