SSIS Web Service Task

The SSIS Web Service Task is used to execute the web service methods. For instance, if you want to store the weather report as an SQL extra column then you can consume the Web service provided by the Yahoo or MSN weather, and store the output in a variable so that you can pass the variable to Data Flow Task.

NOTE: The SSIS Web Service Task uses the HTTP Connection Manger to connect with the Web service and to send or receive Files.

In this article we will show you, how to consume the web service methods using the SSIS Web Service Task with example. Before we start configuring the Web Serve Task in SSIS, Let us see the list of available method in our service.

Below screenshot shows you the Methods available in this web service. Our task is to consume the GetCityForecastByZip method available in this web service. Please visit http://wsf.cdyne.com/WeatherWS/Weather.asmx website to use the same service.

SSIS Web Service Task Example

Drag and drop the Web Service Task into the Control Flow region and rename it as the SSIS Web Service Task

Double click on it will open the Web Service Task Editor to configure it.

Name: Please provide the Unique Name for this web service task
Description: Briefly describe the Task Functionality. It is always a good practice to provide the valid description.

HttpConnection: An HTTP Connection enables this SSIS package to access the web services. It used the HTTP to send and receive the files. By clicking on the drop down arrow will show you the already created HTTP Connections (If any) or please click on the <New Connection..> option to create one. Let us see what happen when click on the <New Connection..> option

Once you click on the <New Connection..> option, HTTP Connection Manager Editor will be opened to configure it.

Server URL: Please specify the URL of the web service. If you are planning to download the WSDL file using Download WSDL button, type the URL of the WSDL file otherwise choose the service URL.
Use Credentials: If you want the HTTP Connection Manager to use the security credentials of a user, Please check mark this option.
User name: Please specify the user name to access the Service.
Password: Please specify the password to access the Service.
Domain: Please specify the domain here.
Use client Certificate: Please specify whether you want to use the client certificate as the authentication or not.
Time-out (in seconds): Please specify the web service connection time out in seconds. If the connection takes more than this time then the connection will fail.
Chunk size(in KB): Please specify the chunk size for the writing data.
Test Connection: This button will help us to check whether we successfully established the connection to web service or not. Based on the result we can alter the connection settings.

In this example we are selecting the Web service from free source so we don’t have to configure the credentials and proxy settings. From the below screenshot you can observe that, we are providing the Service URL: http://wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL because we want to download the WSDL file.

Click OK to close the HTTP Connection Manager.

WSDL File: If you already downloaded the WSDL file then we have to select the .wsdl file from your local file system Otherwise, create an empty file with wsdl extension in your local hard drive and then click on the …. button besides the WSDLFile option.

Once you click on the …. option, a new window will be opened as shown in the below screenshot. Using this we can create New wsdl file in your local file system or select the Existing File from the file system. From the below screenshot you can observe that, we are selecting the existing file called web.wsdl

OverwriteWSDLFile: This property has two options: True and False. If we set this property to true then Web Service Task will overwrite the existing WSDL files in the local file system.

Next, click on the Download WSDL button to download the WSDL file and if exists, it will overwrite the existing file.

Next, click on the input tab to Specify the service Service and methods.

Service: Please select the required service from the list of available web services.
Method: Here it will display the list of available Method in above selected web services so, Please select the required method

Service: By clicking on the drop down arrow will show you the available services so please select the required one. Here we have only one service and we are selecting the same.

Method: By clicking on the drop down arrow will show you the list of available methods. From the below screenshot you can observe that, we are selecting the GetCityForcastByZip method. This method will accept the Zip code as a parameter and display the weather forecast.

Since this method will accept the Zip code as a parameter. We are providing the Zip code of California. Here we have an option to choose the variable also.

Next, click on the Output tab to configure the Output data.

If we set the OutputType to File connection then we have to configure the Output Connection as File Connection Manger. It means the output xml data will be stored in a local file system.

If we set the OutputType to Variable then we have to configure the Output Connection as Variable. It means the output xml data is stored in a variable. This option is very useful because we can use this xml data in other tasks or transformations.

Once you click on the <New Connection..> option, a new window called File Connection Manager Editor will be opened as shown in the below screenshot. Using this we have to configure the Output Connection.

Click on the Browse button to create New XML file available in your local file system or select the Existing File from the file system. Please refer File Connection Manager article to understand the configuration.