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 Services I 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
The 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 SQL Server.
Overview of the Web Service Task
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 document.
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 Google Inc. The XML 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.