Asynchronous script component in SSIS for web service call

Question

I have to create a SSIS package for the following scenario. First, I have to do a database query . A set of Ids is returned from the query. For each Id , I have to do a web service call which will further call some wcf services sequentially.
The set of ids returned from the database is very large. If I do a web service call for each Id sequentially , it will take a lot of time. So, basically I need to make async call to web service.
Only way I see to solve this problem is to use asynchronous script component of SSIS for calling the web service. Other way would be to call the web service inside a thread in the synchronous script component.
What is the right way to solve the problem or there is any other better approach?

Answer 1

Try this:

• Pull the IDs into a 'queue' table in SQL Server
• Create N dataflow components.
• Have each dataflow component pull an id from the queue and make the call.

If you create 8 dataflow components, you can run 8 lookups in parallel. Each one will take an id when it is complete.

Here are some references for the framework:

• http://www.simple-talk.com/sql/ssis/ssis-and-parallelism-the-unseen-minions/
• http://josef-richberg.squarespace.com/journal/2010/1/9/implement-a-stack-in-sql-server-using-stored-procedures.html
• http://josef-richberg.squarespace.com/journal/2010/1/13/ssis-threading-presentation-archive-location.html