February 14, 2013 at 7:52 am
I'm still fairly new to SSIS and, though I have no trouble receiving records from a web service, I have been unable to discover how to write records to (i.e., post to) a web service from within SSIS other than, perhaps, scripting. Could someone point me to an article or other resource on how to post to a web service from SSIS? Thanks!
February 14, 2013 at 4:53 pm
Have you looked into the Web Service Task? If so and you found it lacking can you describe more about what you are trying to do?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2013 at 5:18 pm
I'm using the Web Service Task with no trouble to read, and my first assumption since I could see posting methods as an option was that I would be able to write records to the web service using it, but I only see how to read with it. Are there instructions some where?
February 15, 2013 at 5:21 am
It sounds like you are looking for a "Web Service Destination" Component inside the Data Flow Task but you will not find one there. How you might deal with it, without using a Script Component in your Data Flow setup as a Destination which might be the simplest way to go about this I think, is in your Data Flow Task capture the rows you want to send into the web service into a Variable of type Object, i.e. an ADO.NET DataTable. Then, back on the Control Flow use a ForEach Loop Container to iterate over the Variable that now contains a DataTable and call your web service using a Web Service Task inside the loop container.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2013 at 7:00 am
Interesting approach. Thanks for the reply. And you're right -- something like a Web Service Destination was what I was looking for. With your approach, how exactly would I pass the current variable record to the Web Service Task? Using the "Input" tab?
February 15, 2013 at 8:43 am
Carl Waitz (2/15/2013)
Interesting approach. Thanks for the reply. And you're right -- something like a Web Service Destination was what I was looking for. With your approach, how exactly would I pass the current variable record to the Web Service Task? Using the "Input" tab?
When you setup the inputs check the Use Variable box and set it to a valid variable name. In the ForEach Loop Container map the columns from your DataTable into those same variables.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 24, 2013 at 3:18 pm
The suggestion that you have mentioned sounds very good. I am actually trying to do something like that. I need to post data to a web services.
Can you send me an example or a link to where i can find the exact way to do it. Would really appreciate your help.
October 25, 2013 at 1:21 am
I do not have a ready example to send. You can find many examples online of how to call a web service from within C#, and many examples of how to implement a Script Task in SSIS. Between the two you can figure how to call a web service from an SSIS C# Script Task.
If you have more detailed questions when trying to implement, please post back.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 25, 2013 at 7:55 am
My original query is from when I was first acclimating to SSIS with a Web Service which was still under development. I quickly found everything I needed at the time within SSIS web service and XML tasks, though we eventually did have to use a couple of script tasks for new issues that came up. Thank you for the help. Much appreciated.
October 30, 2013 at 9:37 pm
Carl Waitz (10/25/2013)
My original query is from when I was first acclimating to SSIS with a Web Service which was still under development. I quickly found everything I needed at the time within SSIS web service and XML tasks, though we eventually did have to use a couple of script tasks for new issues that came up. Thank you for the help. Much appreciated.
If that was directed to me, you're welcome. Either way, thanks for relaying that you experienced success leveraging SSIS to interact with a Web Service. It's nice to know that after all the research and proofs of concept are done which solutions make the cut and get to go out into the wild.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 30, 2013 at 10:29 pm
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.
October 30, 2013 at 10:35 pm
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
October 30, 2013 at 10:42 pm
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
October 30, 2013 at 11:03 pm
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
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
October 31, 2013 at 6:12 am
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
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply