SSIS Web Service Task

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 is http://www.webservicex.net/uszip.asmx?op=GetInfoByZIP. The rest of the settings for the connection are default.

clip_image002

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.

clip_image004

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.

clip_image006

Under the output node set the Output type to Variable and create a results variable with a datatype of string.

clip_image008

If you enter the zip code of 32065 you will receive back this xml list.

clip_image010

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.

Advertisements
This entry was posted in SSIS, Syndication and tagged . Bookmark the permalink.

2 Responses to SSIS Web Service Task

  1. jagadeesh says:

    Hi Mike ,
    any way to use the webservice on the ssis script task using c# we can use the webservice to post the data from a flat file source or the xml source.

    Any comments ?

    Regards
    Jag

  2. Arul Paulus-Rosenow says:

    Can you point me in the right direction…I am trying to pass the session state from the Web Services Task in SSIS. First I have established the login credentials method and now want to run a query but get an invalid session error. How can I continue using the established http connection to proceed with the remaining api calls using the SSIS Control Flow Web Service Task?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s