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. I have a blog on the XML task also.
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 ishttp://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 datatype 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.
