Consuming from Web Service and Load data into SQL

Hi:

Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.

MA,

Let me clarify. Do you want to consume data from a web service from within the data-flow?

-Jamie

|||

Well, the goal is to call a web service, and pump data into SQL server, although I thought it's less complex to hook up to a Web Services Task, and then utilize the output from the Web Services within a DFT somehow but not sure. Is there a better way to do this. Thanks.

|||

I think so, yes. It is possible to consume data from a web service from directly within the pipeline. What you are proposing would be an extra step.

To consume from a web service in the pipeline you will need a script component. Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/ref=pd_bbs_sr_1/104-7087211-5731917?ie=UTF8&s=books&qid=1181582618&sr=8-1) has a chapter explaining how to do it.

-Jamie

|||

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

|||

MA2005 wrote:

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

Fair enough. I think that's a worthy aim.

Out of interest, why do you not want to use the script component?

-Jamie

|||

No reason, just exploring an alternate solution. :-)