Hello all,

Actually in my current company we do have SSIS packages that call a API to update/insert data.

I am a DB Dev and did use SSIS package to call web services in script tasks. All I knew at the time was SSIS and it seemed to be a perfect fit. It worked for us but..

A couple things that we ran into:

1.Looping through each record 1 at a time calling the web service. No parallel processing.

2.If the web service fails make sure to capture the error somewhere in an error message that is understandable. For example we would get errors in the SSIS package stating "Error in Script Task". This gives us nothing to trouble shoot. So definitely check the error handing and message returned.

3.No automatically retry if deadlock, unless you have this in the Stored proc. If loop fails during because 1 record was deadlocked (example already processed 100 records) then entire package will fail (unless you set up additional error handing in web service). It would be nice to retry if deadlock/timeout.

We really wanted to have retry and more parallel threads picking up this data. The more the better.

We tried a different approach (wcf, msmq, tables, and some program that can call wcf –3rd party product for us)

Here is what we did.

1. Create a physical table in the database for the data we are processing. Example ProcessQueue

2. A dev would insert into this table and have all the columns needed for the service.

3. Create a WCF service that would pull from the ProcessQueue (physical table) and then add this data to MSMQ. MSMQ has built in retries. Then the same wcf service consumes this data on the queue and processes it.

4. Have a windows task/windows service/automate/talend (whatever can call a wcf service/web service) run every 1 minute.

5. After processing the data, it is added to a ProcessLog table which tracks the status of the transaction. Failure/success and result message.

We delete the records from the ProcessQueue table after they are added to MSMQ. Only those records that need to be processed are in this table.

Depending on how many servers CPU's are behind the F5 (if using), is the number of records that can be picked up from the MSMQ.

Now I know this is something much different than using SSIS, but in the end it worked out. It took me some time to understand and I am still in the process.

We are able to process so many more transactions per minute than we ever could with SSIS. The automatic retries with MSMQ is awesome. The use of the "log" tables to track the status helped debug the problem easily.

Oh and the web service task in SSIS never seemed to work. It would not take the WSDL for some reason. Perhaps to complex. This lead to the C# coding in the script task.