SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Post to a web service from SSIS?


Post to a web service from SSIS?

Author
Message
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 2057
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39490 Visits: 14411
Very cool! Did you ever look into using Service Broker (SB)? I am sure there is much more nuance to the system and what it provides than what you cold relay in a Forum post, but it sounds at least like some of the data movement might have been avoided if you had employed SB in place of MSMQ and the physical "queue" table you built.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 2057
Hey opc.three,

Yes that is something I am looking at now to see how it can be leveraged. We are not only a sql server shop, but perhaps adding to the service broker queue can call a wcf service.

So yes I am on it now.

Thanks,
Brad
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 2057
Here is what I found. Thanks opc.three!

Using Service broker (SQL Server) to call a WCF service which could add data to the queue. There are tables related to the service broker as well which contain the messages so perhaps this can be used to replace the Queue Physical tables in some cases.

Get Started With Using External Activator - SQL Server: Service Broker Team Blog - Site Home - MSDN Blogs
http://blogs.msdn.com/b/sql_service_broker/archive/2009/05/18/get-started-with-using-external-activator.aspx

Service Broker Scalable Web Service Calls From SQL Database - Sergey Maskalik's
http://blog.maskalik.com/sql-server-service-broker/scalable-webservice-calls-from-database

Service Broker And Web services - Stack Overflow
http://stackoverflow.com/questions/3397278/service-broker-and-web-services
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51390 Visits: 21156
Here's another version for lazy people:

Using Service broker (SQL Server) to call a WCF service which could add data to the queue. There are tables related to the service broker as well which contain the messages so perhaps this can be used to replace the Queue Physical tables in some cases.

Get Started With Using External Activator - SQL Server: Service Broker Team Blog - Site Home - MSDN Blogs
http://blogs.msdn.com/b/sql_service_broker/archive/2009/05/18/get-started-with-using-external-activator.aspx

Service Broker Scalable Web Service Calls From SQL Database - Sergey Maskalik's
http://blog.maskalik.com/sql-server-service-broker/scalable-webservice-calls-from-database

Service Broker And Web services - Stack Overflow
http://stackoverflow.com/questions/3397278/service-broker-and-web-services


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search