posted 4/6/2010 by MikeDavis - Views: [19833]
The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can then parse the XML to find the node values you need. This can be done with the XML task in SSIS. Click here to see my blog on the XML task.
The Web Service I am using is a free demo service. It allows you to enter a zip code and returns the city and state associated with that zip code. This first thing you need on the package is an HTTP connection. The Server URL for this connection is http://www.webservicex.net/uszip.asmx?op=GetInfoByZIP. The rest of the settings for the connection are default.
Then drag a Web Service Task into the control flow. Set the connection to the HTTP connection you just created. You will need the WSDL file for the web service. This can be downloaded from the website where the web service is hosted. This file will need to be saved locally. Set the WSDL File to the location where you saved the WSDL file in the Web Service task in SSIS.
Click on the input node on the left window pane and set the input properties as shown below. These are drop down menus that are populated automatically by the WSDL file. Create a package variable with a data type of string to hold the zip code. Map that in the fields below as shown.
Under the output node set the Output type to Variable and create a results variable with a data type of string.
If you enter the zip code of 32065 you will receive back this xml list.
This shows us the proper city and state associated with the zip code we entered. Now we can parse through the XML with an XML task and use this data. I show you how to do this in the next blog here.
I have a problem, I need to call a Web Service using a SOAP call. I realise that I need to use a Web Services task as part of the dataflow rather than control flow, but I am unsure as to how to acheive this. Does anyone have any pointers? Thanks
Senion, You can't call it from inside a data flow. But you can stage the data in the control flow by loading an object variabe with the data, then do a for each loop over the variable. Place the web services task in the for each loop. You are limited on the size of the object variable though.
Mike, I was able I have been trying to pull data from a web service using this method and SSIS 2008 R2, however evertime I attempt to set "Input" screen options on the task, BIDS crashes. The Webservice I am calling only has SOAP 1.1 and SOAP 1.2 retrieve methods (no HTTP GET nor HTTP POST). I'm not sure if that is the issue or not, any ideas?
-mwpage
If BIDS crashes then you might have a bad install. That is my guess. I would reinstall or try from a different machine. If it happens on a different machine, then it seems to be an issue with the web service. Sorry I could not give more help.