Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Example using Web Services with SQL Server Integration Services


By:   |   Read Comments (11)   |   Related Tips: More > Integration Services Development

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 http://www.webservicex.net/globalweather.asmx web service and click on the "GetWeather" link.

    Integration Service Project

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

    Integration Service Project

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

    Integration Service Project

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.

    Integration Service Project

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

    Integration Service Project

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

    Integration Service Project

  4. In the Server URL textbox, specify the web service address: "http://www.webservicex.net/globalweather.asmx?WSDL" and press the Test Connection button.

    Integration Service Project
  5. Create an empty wsld file.  For this example I created a file called mywsld in the "C:\Webservice" folder.

    Integration Service Project
  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.

    Integration Service Project

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

    Integration Service Project

  8. In the Output tab, in the File properties, select "New Connection".

    Integration Service Project
  9. Select the "Create file" option in the Usage type.

    Integration Service Project

  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.

    Integration Service Project
  11. Run the SSIS project.

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

    Integration Service Project

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


Last Update:






About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, January 31, 2017 - 1:21:35 AM - srinivas Back To Top

Hi 

, I have been using similar for  a daily currency exchange rate.

when I ran websrvice package some time its returning -1 as usd to GBP conversion.can you please tell  me why its returning -1 instead of orginal values.please tell me how to resolve for this sistuvation  in ssis webservice task

 

 


Sunday, January 08, 2017 - 9:22:01 PM - Adam Back To Top

Hello and great article.  I am trying to get data utiizing ssis from a web based api source similar to the above where I first must send a user ID and retrieve a token to place within the web call to get the data.  The token expires every 2 hours and I am looking to retrive data several times an hour.    Any suggestions or direction for a beginer would be highly appreciated

 


Monday, October 03, 2016 - 3:28:03 AM - Mohan Back To Top

Hello Daniel,

I tried doing the same as u suggested above, I have got an error and my WEB Service task doesnt run.

Error : 

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Could not create an object of the type ProxyNamespace.UnallocatedLaborHours[]&. Check whether the default constructor exists.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

 

I tried on google and found no help, would u please help me if possible

 

Note: While creating a new connection for the Web service i dint touch the proxy part as i dont have a proxy. 

 

Thanks a ton 

Mohan


Monday, February 08, 2016 - 3:37:29 AM - Rohit Back To Top

Hi Daniel,

 

 I have been trying to use the variable option as an input to the WebService but Webservice doesn't work and I have to re-download the wsdl to get the flow working.

The WebService works only with the static input option, but as soon as I select the Variable option the WSDL gets corrupted. Please let me know if you have a solution on the same or need more information.

 

Thanks,

Rohit 

 


Tuesday, December 08, 2015 - 8:40:47 AM - Scott Stanek Back To Top

Been using your example for about six months to get a daily currency exchange rate. It stopped working (the site) on 11/21/15 and has been returning -1 as the EUR to USD conversion since then (and SAR, CNY, BRL). Tried to contact them but Support@webservicex.net is bouncing.

If you have any means of contacting these folks to let them know there is an issue, that would be great. If it is just a "no longer supported" thing --well I guess I need to look elsewhere.


Wednesday, February 25, 2015 - 2:21:24 PM - Daniel Back To Top

I would use Visual Studio.

http://www.codeproject.com/Articles/8257/How-to-make-a-simple-WebService-and-consume-it


Wednesday, February 25, 2015 - 12:24:43 PM - Daniel Back To Top

Hi, great article.

Can someone tells me how (step by step) to create a .wsdl empty file.

I have try with eclipse and notepad++ and i can't figure it out.

Thanks


Monday, February 02, 2015 - 12:22:05 PM - Mike D Back To Top

HI:  I have done as you say and it works just like your example.  The issue seems to be that the result XML is all within a <STRING> tag and the < and > are HTML encoded.  The XML Task needed to transform this data requires that the XML not be Within a <STRING> tag or encoded.  How can the resultant XML be set to not use the <STRING> format?

Thanks!


Thursday, November 13, 2014 - 5:58:05 PM - venus Back To Top

Hey Daniel,

A quick question for you.

Why I can't connect to the web service showed in your ariticle?

It says unable to connect to the remote server.

Thanks

Venus


Monday, September 29, 2014 - 11:54:24 AM - Jason Back To Top

I've spent three days searching/fighting with the web services aspect and trying to pull them down to my SQL server instance, to no avail.  My webservice is IIS involving ArcGIS.  The page I'm trying to get information from is not a '.asmx' page, rather, it's a form that accepts 4 parameters (two zipcodes, a date, and an object type respectively).  I can select the format coming out (html or json) and I can select the method of how I want to receive it, either by GET or POST.  I create the HTTP connection manager, the connection works, and I overwrite the wsdl file I created, but the file doesn't contain any WSDL informaiton.  I move to editing the inputs, and get the error that the input WSDL file is not valid and that there is an error in the XML document (10,2).  How can I get this to work with this form so that I can send the parameters up and get the string of information back to fill the rest of my query requirements in SQL Server?

Thanks.  Below is what I'm using so you know the rest of the playing field.

SQL Server 2012

Visual Studio 2010/2012 (access to both)


Wednesday, July 09, 2014 - 7:28:03 AM - Deepak Sharma Back To Top

Hi Daniel,

Very nice article. I want to do the same in SSRS. My requirement is to create a report which will show the temperature of City - will be provided through Report Parameter.

Request if you may please share your next post on SSRS.

 

 


Learn more about SQL Server tools