Using Web Service and XML Tasks in SQL Server 2008 Integration Services

9.       Drag and drop Web Service Task from the toolbox to the Control Flow window and rename it as “Web Service Task - Get Google StockQuote”. Then double click the Web Service Task to open up the editor window as shown in the snippet below.

Within the Web Services Task Editor, click on General tab in the left panel and then from the right side panel under Connection choose HttpConnection value as “HTTPConnectionToWebService”. Next, provide the path for WSDLFile as “C:\StockQuote\StockQuote.wsdl”. Next, set the value as “True” for OverwriteWSDLFile option. When the value is set to “True” it means each time the package executes it will download a new copy of StockQuote.wsdl file and store it locally. Click Download WSDL button to manually download the StockQuote.wsdl file.

10. Select Input tab on the left side panel of Web Service Task Editor and choose StockQuote as the value for Service. Similarly, select GetQuote as the value for Method option from the dropdown list.

As highlighted in the snippet above choose “User::StockQuote” as the value from the drop down list and also enable the checkbox. This variable holds the input parameter “GOOG”.

11.    Select output tab on the left side panel of the Web Service Task Editor and choose OutputType as File Connection, and for File option choose the path as “C:\StockQuote\StockQuote.XML”. Click OK to save the Web Service task.

12. Create a StokeQuote.XSD schema file by copying the below XML code and save the file in the “C:\StokeQuote” folder. This schema file will be used by the XML task to validate the resulting XML received once the GetQuote method of the StockQuote web service has executed successfully.

<?xml version="1.0"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

 <xs:element name="string" type="xs:string" />

</xs:schema>

13. Drag and drop  “XML Task” from the toolbox to the Control Flow window. Then double click the XML Task to open up the editor window and set the properties under general tab as shown in the snippet below.

The XML Task Editor should be configured with the details as shown in the below table.