SSIS – Starter kit for the uninitiated – Web Service Task

Continuing from the last post, we now configure the web Service task.

If you visit the web service link you will see the below screen

http://free-web-services.com/web-services/geo/location-info/

Click the Link for Airport Information, it will take you to the below screen where you can find the URL for the web service.

 

Click the URL to go to the methods page. Here you can see that we can get airport info via a number of different methods, we are choosing to get the list of airports by country.

 

Clicking the link above will take you to the form where you can enter the name of the Country and the response will look as shown below.

 

All we need to do now is perform all these steps programmatically via SSIS.

 

StepScreenshot
Double click the Web Service task we added in the previous post.
We need to configure it as shown in the adjacent screenshot. The First step is the http connection which defines the URL from which to fetch the Web Service

In our case the URL is

http://www.webservicex.net/airport.asmx

Click

Enter the URL in the textbox and press OK ,

If there is a proxy of any other information required for our laptop to connect to the internet, enter it under the proxy Tab.

Press OK

Next we need the WSDL file for the web service. The WSDL file is like the XSD for an XML file. It defines how to interpret the web service.

You can download the WSDL file manually and point to the file by using a file connection manager.

The Download WSDL button can do this automatically for you but it doesn’t work for all sites e,g this now.

In order to download the WSDL manually

Copy paste the below URL in your browser

http://www.webservicex.net/airport.asmx?wsdl

As you can see it simply needs to append the text ?wsdl to the web service URL

You will see the adjacent screen

IMP:- the text displayed in the browser is not the WSDL instead right click on the screen and click Save Page As and save the file to disk

The text within the notepad is the actual WSDl content.
Click the Ellipse button Next to WSDL File as shown and navigate to the path where you saved the File.
Now that the task know which URL to use and how to understand the data going in and coming out from it all we need to do is pass out list of CountryNames to it.

Click the Input link on the nav bar and select Airport as the service. Under which we need the Method GetAirportInformationByCountry

The webmethodDocumentation will appear automatically.

Since we are passing country names dynamically we tick the checkbox under Variable and the variable we defined previously needs to be selected from the list

Now that it know which values to ass as input, all that’s left is to define where to store the output.

Click the option for Output from the nav bar and select under File.

Create a File on the hard disk and rename the extension to xml. Navigate to this file by clicking the browse button.

Press OK twice. Congrats you have now configured the Web service Task. However we are not done.

If we run the ForeachLoop container now it will overwrite the file with every iteration.

 

We need to tell the WebService to create a new file for each country from the foreachloop container.

Next we explore how to configure tasks to behave dynamically.