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 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.
In this article, we show you how to consume the web service methods using the SSIS Web Service Task with example. Before we start configuring the web service Task in SSIS, let us see the list of the available method in our service.
NOTE: The SSIS Web Service Task uses the HTTP Connection Manager to connect with the Web service and to send or receive Files.
The below screenshot shows you the Methods available in this SSIS web service Task. Our task is to consume the GetCityForecastByZip method available in this web service. Please visit https://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 SSIS Web Service Task Editor to configure it.
- Name: Please provide the Unique Name for this web service task
- Description: Briefly describe the SSIS Web Service Task Functionality. It is always a good practice to provide a valid description.
HttpConnection: An HTTP Connection enables this SSIS package to access 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 SSIS Web Service Task.
- Server URL: Please specify the URL of the web service. If you are planning to download the WSDL file using the 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 SSIS Task example, we are selecting the Web service from a 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: https://wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL because we want to download the WSDL file.
Click OK to close the HTTP Connection Manager for SSIS Web Service Task.
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 beside the WSDLFile option.
Once you click on the …. option, a new window will open. Using this, we can create a 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 choosing the existing file called web.wsdl
OverwriteWSDLFile: This SSIS Web Service Task property has two options: True and False. If we set this property to true, the 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 it exists, it will overwrite the existing file.
Next, click on the input tab to Specify the SSIS Web Service Task Service and methods.
- Service: Please select the required one from the list of available web services.
- Method: Here, it displays the list of available Methods in above-selected web services. So, please select the required method
SSIS Web Service Task: By clicking on the drop-down arrow will show you the available ones. So, please select the required one. Here we have only one service, and we are choosing 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 accepts the Zip code as a parameter and displays the weather forecast.
Since this method accepts 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 SSIS Web Service Task Output tab to configure the Output data.
If we set the OutputType to File connection, we have to configure the Output Connection as File Connection Manager. It means the output XML data 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 stored in a variable. This option is handy 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 opened. Using this, we have to configure the SSIS Web Service Task Output Connection.
Click on the Browse button to create a New XML file available in your local file system or select the Existing File from the file system. Please refer to the File Connection Manager article to understand the configuration.
From the above screenshot, you can observe that we are creating a new file called WeatherForecast.xml file.
Click OK to finish configuring and closing SSIS Web Service Task Editor. Let us run the package to check whether we consumed the web service or not.
Let us open the WeatherForecast.xml file and see. From the below screenshot, you can see that we achieved the result.
