SSIS Call a web service

A web service is a method over the network used to share information between applications. Web services are very common these days to retrieve game matches, the weather, stock quotes, etc.
In this tip, we are going to work with a web service that pulls weather information.  To begin with, your server running SSIS must have internet access.

Sample Web Service - Global Weather

1. Using your browser, open the http://www.webservicex.net/globalweather.asmx web service and click on the "GetWeather" link.
   
   
   


2. Specify the City and the Country and press the "Invoke" button.
   
   
   


3. You will be able to see weather information such as wind, temperature and other related information in an XML format.
   
   
   

As you can see, this web service is simple. It is a XML file with results that are published on the web and you can invoke it with your application. Let's retrieve this information using SSIS.

Setting up SSIS package to call a web service

1. Open SSDT or BIDS and create a new SSIS Project.
   
   
   


2. Drag and drop the Web Service Task onto the Design pane.
   
   
   


3. Double click on the Web Service Task and create a new HTTP connection.
   
   
   


4. In the Server URL textbox, specify the web service address: "http://www.webservicex.net/globalweather.asmx?WSDL" and press the Test Connection button.
   
   
   
5. Create an empty wsld file.  For this example I created a file called mywsld in the "C:\Webservice" folder.
   
   
   
6. In the WSDL File property specify the path of the file created in the step 5. Make sure that the OverwriteWSDLFile option is set to true and press the Download WSDL button.
   
   
   


7. In the Input tab, specify the City and the Country. In this sample, we used New York and United States. Press OK.
   
   
   


8. In the Output tab, in the File properties, select "New Connection".
   
   
   
9. Select the "Create file" option in the Usage type.
   
   
   


10. In the File textbox, specify the path of the file to store the results of the Web Service and press OK. This is the file where the XML data will be written to when the package is run and the web service is called.
    
    
    
11. Run the SSIS project.
    
    
    
12. Open the file created in the path specified in step 10 and you can see the XML output that was created from the web service.
    
    
    

As you can see, the process to invoke a Web Service is very simple. If you have any questions, feel free to write your comments and questions below.