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
Carl Waitz
Carl Waitz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 70
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!
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
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
Carl Waitz
Carl Waitz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 70
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?
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
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
Carl Waitz
Carl Waitz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 70
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?
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
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
sindhuv.tx
sindhuv.tx
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 172
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
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
Carl Waitz
Carl Waitz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 70
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
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
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