Example using Web Services with SSIS

Problem

In this tip, we will learn how to work with Web Services using SQL Server Integration Services (SSIS).  We will use a simple example to call a web service from SSIS and store the results in a file.

Solution

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 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.

Next Steps

• Take this further and read the created XML file and import directly into your database.  Also, pass in the parameters for city and country dynamically.  Also explore other web services to get data such as stock quotes, sports scores and more.
• For more information, refer to the following links:
  • http://technet.microsoft.com/en-us/sqlserver/ff686773.aspx
  • All SQL Server Business Intelligence Tips

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018