Using Web Service and XML Tasks in SQL Server 2008 Integration Services
SQL Server Integration Services (SSIS) is a
Business Intelligence tool which can be used by database developers or
administrators to perform Extract, Transform & Load (ETL) operations. In my
previous article entitled Using the File System Task in SQL Server 2008 Integration
discussed how to use the File System Task which is
available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Web Service
and XML Tasks which are available in SQL Server 2005 and later versions.
Overview of Data Preparation Tasks in
SSIS 2005 & Later Versions
data preparation tasks within SSIS can be used to retrieve data or to validate
the quality of data. The different types of data preparation tasks which are
available in SSIS are the File
System Task, the FTP Task, the Web Service Task, the XML Task and the Data
Profiling Task. If you are new to SQL Server Integration Services then I would
recommend you to start with my previous article entitled SQL Server 2008 Integration Services Tasks to
understand various types of Integration Services Tasks which are available in
Overview of the Web
The Web Service Task is a new task which
was introduced in SQL Server 2005 Integration Services. Using the Web Service
task you can connect to a web service and execute any one of the available web
methods which are exposed by the Web Service. Once the web method is executed,
it returns an XML results and the result set can either be written to a
variable or to an XML file.
Overview of the XML Task
The XML Task can be used to perform
different types of predefined operations on XML Data. Using XML Task you can to
dynamically merge, modify or create XML files during the SSOS package runtime.
The different types of operations which can
be performed by the XML Tasks are shown in the below snippet.
Validate: – The validate
option can be used to validate an XML document against an XML Schema Definition
(XSD) or a Document Type Definition (DTD).
XSLT: – The term XSLT stands for Extensible Style
sheet Language Transformations. This can be used to perform XSL transformation
against any XML document.
XPATH: – Using XML Path Language (XPATH) option,
one can extract a section or a specific nodes data from a very large XML
Merge: – The
merge option can be used to merge two XML documents having the same XML
structure into one single XML document.
Diff: – One can use this option to compare two
XML documents and to produce a third XML document called XML Diffgram. XML
Diffgram document will have all the difference between the two XML documents.
Patch: – It can be used to apply the results of
a Diff operation to an XML document thereby creating a new XML document.
Example using Web Service
and XML Tasks
We will run through an example where I will
be using an XML Web Service named StockQuote available for free
from www.webservicex.net. You can access the XML
Web Service directly from http://www.webservicex.net/stockquote.asmx?wsdl. In this example,
we will be creating an SSIS package which will use a Web Service Task to determine
latest Stock Quote for
result set received from the Web Service task will be saved to an XML file
named StockQuote.XML. Finally using the XML Task, we will be validating StockQuote.XML
file against the StockQuote.XSD which we have written manually to verify
the resulting StockQuote.XML file returned by the web service.
1. Create a new SQL
Server Integration Services Project and rename the default package WebServiceTaskExample.dtsx
2. Double click the WebServiceTaskExample.dtsx
package to open it up in Design mode.
3. Add variables named
StockQuote; you can open up Variables window by right clicking within the
control flow design window and then choosing the Variables option from the
drop down list as shown in the snippet below.
4. Once the variables
window opens up, click the Add Variable button and set the value for the StockQuote
variable to GOOG (which is the stock code for Google Inc. )
5. Create a new folder
named StockQuote within C Drive. The above mentioned files such as StockQuote.XML,
StockQuote.XSD etc will be stored in this folder.
6. Right click within
the Connection Managers windows and choose New Connections… from the popup
window. This will open up Add SSIS Connection Manager window where you need to
choose HTTP and click Add… button to configure HTTP Connection Manager.
7. Within the HTTP
Connection Manager Editor window you need to provide the Server URL for StockQuote
web service. The WSDL file related to StockQuote web service is available in http://www.webservicex.net/stockquote.asmx?wsdl location. You also
need to change the default connection time-out value from 30 seconds to 120 seconds;
this change will avoid web service from timing out in case of poor network connection.
Once you have entered all the details as shown in the snippet below click on
Test Connection button to test the web service connectivity. Finally click OK
to save the changes and then rename the HTTP Connection Manger as HTTPConnectionToWebService
by right clicking the same from the Connection Managers window.
8. Create an empty file
named “StockQuote.wsdl” within the “C:StockQuote” folder. The StockQuote.wsdl
file will be used by the Web Service Task to store WSDL file which is
downloaded from http://www.webservicex.net/stockquote.asmx?wsdl link.