Example using Web Services with SQL Server Integration Services

By:   |   Comments (20)   |   Related: 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, June 18, 2021 - 3:49:34 PM - Mike Back To Top (88876)
This won't work if they are using TLS 1.2

Wednesday, April 10, 2019 - 8:07:19 AM - Lucas Martins Back To Top (79516)

Hi guys,

I am using third party Web Service and I am having issue on step 7 "In the Input tab, specify the City and the Country. In this sample, we used New York and United States. Press OK." After wsdl file download (step 6) when I qlik on "Input" (step 7), SSIS show the message below:

"Could not read the Web Service Description Language (WSDL) file. The input WSDL file is not valid. The following error ocurred while reading the file. There is an error in XML document (0,0)"

Anyone have the same problem?

Best Regards
Lucas

Thursday, January 10, 2019 - 7:28:20 AM - maryam Back To Top (78691)

 Thanks for your post, But also it's very important to know how to create an empty wsdl, I am 2 hous with it and I can't figure out how?

Tuesday, July 24, 2018 - 9:07:37 AM - LeoG Back To Top (76806)

I'm getting this error when I try to download wsdl file but URL works and displays wsdl xml date in the browser.

Any help?

 

TITLE: Web Service Task
------------------------------

SSL certificate response obtained from the server was not valid. Cannot process the request.


------------------------------
BUTTONS:

OK
------------------------------

Sunday, May 27, 2018 - 2:37:36 AM - M Back To Top (76028)

 

 hi daniel,if it is possible for you put a post with calling a web service in ssis with complex type outputs like table output,and please tell me how i can combine two sting variable to set  input parameters value with this new parameter?

tnx.

Tuesday, April 3, 2018 - 3:40:21 PM - Jon Young Back To Top (75593)

 The page http://www.webservicex.net/globalweather.asmx no longer exists.  Can you please provide another workable URL?

Tuesday, October 24, 2017 - 11:59:58 AM - Nancy Gold Back To Top (68748)

HI Daniel Calbimonte -

i am trying to use wsdl bwo ssis for the first time.

in the example above, when clicking the Invoke button the following is returned:

<?xml version="1.0" encoding="UTF-8"?>
<string xmlns="http://www.webserviceX.NET">Data Not Found</string>

what have i done incorrectly?

thanks in advance for your assistance.

Regards -

N

 

Thursday, September 14, 2017 - 11:57:06 AM - Douglas Vinicios Lacerda Back To Top (66286)

hi mi Friend

 

I would like to retrieve information from that webservice to directly to database using a store procedure but, it is returning null. Do you know why? follow are the stores procedures that I'm running, the fisrt one named "GetCountry" is not working and the secound "P_CALLWS" works well

CREATE PROCEDURE [dbo].[GetCountry]

@ParamsValues varchar(20)

as

declare @obj int;

declare @valorRetorno int;

declare @Url varchar(1024);

declare @resposta varchar(8000);

declare @hr int

declare @scr varchar(255);

declare @desc varchar(255) ;

 

 

 

set @Url = 'http://www.webservicex.net/globalweather.asmx/GetCitiesByCountry?CountryName='+ @ParamsValues

 

    exec SP_OACREATE 'MSXML2.ServerXMLHttp', @obj OUT

    exec SP_OAMETHOD @obj, 'Open', NULL, 'GET', @Url, false

    exec SP_OAMETHOD @obj, 'send'

    exec SP_OAGETPROPERTY @obj, 'responseText', @resposta OUT

 

 

select @resposta[responsta]

 

exec sp_OADestroy @obj

_____________________________________________________________________________________________________________________

 

CREATE PROCEDURE [dbo].[P_CALLWS] @V_PARAM VARCHAR(20) = NULL

AS

    

DECLARE @V_OBJ INT;

DECLARE @V_URL VARCHAR(200);

DECLARE @V_RESPONSE VARCHAR(8000);

 

-- SET @V_URL = 'https://viacep.com.br/ws/17501441/xml/'

SET @V_URL = 'https://viacep.com.br/ws/' + @V_PARAM + '/xml/'

EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @V_OBJ out

EXEC SP_OAMETHOD @V_OBJ, 'OPEN', NULL, 'GET', @V_URL, FALSE

EXEC SP_OAMETHOD @V_OBJ, 'SEND'

exec SP_OAGETPROPERTY @V_OBJ, 'responseText', @V_RESPONSE out

  

SELECT @V_RESPONSE RESULT

 

EXEC SP_OADESTROY @V_OBJ

 

RETURN

 

Thursday, August 10, 2017 - 12:10:50 PM - Ashok Chauhan Back To Top (64479)

Hello Daniel,

Is it not possible to store csv formatted data using Web Service Task? Because I can see only the XML output is being stored in Web Service Task.

Thank You!

Regards,

Ashok Chauhan

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

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 8, 2017 - 9:22:01 PM - Adam Back To Top (45187)

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 3, 2016 - 3:28:03 AM - Mohan Back To Top (43478)

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 8, 2016 - 3:37:29 AM - Rohit Back To Top (40619)

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 8, 2015 - 8:40:47 AM - Scott Stanek Back To Top (40214)

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 [email protected] 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 (36350)

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 (36349)

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 2, 2015 - 12:22:05 PM - Mike D Back To Top (36135)

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 (35281)

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 (34757)

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 9, 2014 - 7:28:03 AM - Deepak Sharma Back To Top (32600)

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.

 

 














get free sql tips
agree to terms