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

Input

OperationType

Validate

SourceType

File Connection

Source

StockQuote.XML

(Browse the file from C:StockQuote folder)

System

True

Output

SaveOperationResults

True

OperationResults

     DestinationType

File Connection

     Destination

Evaluate.txt

(Create a blank file with name Evaluate.txt within the C:StockQuote folder. This file will store the output once the resulting StockQuote.XML from the web service is validated against the StockQuote.XSD file)

     OverwriteDestination

True

Second Operand

SecondOperandType

File Connection

SecondOperand

StockQuote.XSD

(Browse the file from C:StockQuote folder)

Validation Options

ValidationType

XSD

FailOnValidationFail

False

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Using Web Service and XML Tasks in SQL Server 2008 Integration Services”

  1. Thank you SO MUCH for posting this! I had been searching for several days on how to do this.

    Two points:

    1. In step 11, you have “StockQuoteStockQuote.XML” when it should be simply “StockQuote.XML” — and yes, the picture has the correct file name.

    2. In step 12, when creating the StokeQuote.XSD file, readers should be careful with copying/pasting the XML code — I copied/pasted and it changed to be — not sure why, but it did, so create the file and then look it over again. I also re-did all the quote marks. You can open the .xsd file with your browser to make sure it opens properly — the first time I tried it gave me an error and I knew something was up with it.

    Other than those tiny points, it was exactly what I wanted! Thanks so much!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |