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




Array

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 |