Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Post to a web service from SSIS? Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 10:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 PM
Points: 961, Visits: 926
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.
Post #1510017
Posted Wednesday, October 30, 2013 10:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,080, Visits: 12,571
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
Post #1510018
Posted Wednesday, October 30, 2013 10:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 PM
Points: 961, Visits: 926
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
Post #1510019
Posted Wednesday, October 30, 2013 11:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 PM
Points: 961, Visits: 926
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
Post #1510021
Posted Thursday, October 31, 2013 6:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1510138
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse