Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 12, 2007

SQL Server 2005 Integration Services - Part 40 - Web Service Task

By Marcin Policht

In this installment of our series dedicated to SQL Server 2005 Integration Services, we will review a Web Service Control Flow task that exposes an infinite range of easily deployable features by leveraging capabilities present in the .NET Framework. We will explain the underlying principle of this methodology, describe characteristics of its SSIS implementation, and conclude with an example demonstrating its use.

With the introduction of its .NET architecture, Microsoft adopted a different approach to software development (also embraced by other vendors, following specifications defined by the World Wide Web Consortium), which extended the scope of the distributed computing paradigm. One of its rudimentary concepts was the ability to develop independent software components encapsulating a specific function (or set of related functions) that can be easily shared across heterogeneous environments (as well as incorporated into other solutions, regardless of their platform or implementation details) through utilizing well-established, public, Internet-based standards such as TCP/IP, HTTP, XML (eXtensible Markup Language - providing universal data representation format), SOAP (Simple Object Access Protocol - governing rules for accessing software components and interacting with them), and UDDI (Universal Description, Discovery, and Integration - serving as a public directory where individual programs and their purpose can be published and, subsequently, easily located). In order to function properly in distributed computing scenarios, such programs need to be not only self-contained but also self-describing (listing both their purpose and the parameters required to properly execute them and interpret their results). This is accomplished by associating each of them with an XML-formatted Web Services Description Language (WSDL) document. Combination of these interrelated technologies and components is collectively known as Web Services.

Web Services gained popularity primarily through their use as simple applets delivering stock quotes and weather forecasts, generating barcodes, translating text from one language to another, converting between different measurement units or currencies, or verifying address information (you can find some of their free samples on such web sites as WebserviceX.NET or XMethods). With the number of Internet-based applications steadily growing, it is likely that Web Services will become more prevalent in developing Business-To-Business and Business-To-Consumer solutions, offering a variety of more complex features.

Before we start exploring the way to employ Web Services in SSIS, you should first ensure that your SQL Server 2005 installation is at the Service Pack 1 level, since without it, available configuration options are significantly limited (it is not possible to dynamically assign values of Web Service function input parameters). Once you have verified that the SSIS component has been upgraded, launch Business Intelligence Development Studio and initiate a new package of the Integration Services type. Drag the Web Service task from the Toolbox onto the Control Flow tab of the Designer interface and select the Edit... entry from its context sensitive menu. This action will activate the Editor window, which is organized into four sections:

  • General - Besides standard and straightforward properties to set Name and Description, you will find items related to the Web Service connection here. The first one is a name of the HTTP Connection Manager that allows you to specify the URL of a target server hosting the Web Service that you want to utilize (along with credentials or client certificate necessary to establish the connection, its time-out and write data chunk size values, as well as proxy specifications). The second item, called WSDLFile, contains the local file system path of the WSDL file associated with the Web Service identified to by HTTP Connection Manager. In general, there are two ways to configure this setting. The first one requires you to download the appropriate WSDL file manually and store it in the target location (to accomplish this, connect to the Web Services URL, locate its WSDL definition, and save its content on your SSIS server with the .wsdl extension). The second one, which is applicable if the Web Services URL points directly to the WSDL file (i.e. it ends with "?wsdl" string), involves assigning any writeable local file system path value to the WSDLFile property and clicking on the Download WSDL command button in the lower right corner of the Editor window. As long as the URL and target location are valid and accessible, you should receive a confirmation that the WSDL file was downloaded successfully. If the file already exists at the destination, you can overwrite it, providing that the OverwriteWSDLFile property (the third item in this section) is set to True.
  • Input - Once the options in the General section are configured, you are ready to choose a desired Web Service, its method, and assign values to its parameters. Start by selecting the appropriate entries in listboxes for Service and Method items (note that a user-friendly description of each selected method might appear in the WebMethodDocumentation area - providing that its creator decided to include it with the method definition). At this point, you are ready to assign a value to the input parameters required by the method. In the lower right portion of the Editor window, there is a grid with four columns labeled Name, Type, Value, and Variable. The first two describe the name and type of an input parameter, while the last two allow you to provide either an arbitrary value (that will remain constant through every execution of the task) or a variable, which can be dynamically modified at run-time (if the fourth column is not displayed, this means that you have not upgraded your SSIS installation to the SQL Server 2005 Service Pack 1 level).
  • Output - Its purpose is to save the output of the Web Service method selected using entries in the Input section. This is done by choosing either File Connection or Variable in the OutputType listbox and defining either a File Connection Manager (typically pointing to an XML file) or creating a variable where execution results will be stored.
  • Expression - A standard section that allows you to configure properties of the Web Service task dynamically (separate from the ability to assign values stored in variables to input parameters of an individual Web Service method that we described earlier).

Now that you have a basic understanding of Web Service task characteristics, let's try an example demonstrating its use, based on a free sample from one of the Web sites referenced earlier in the article, which lists the Currency Exchange Rate (as a float data type number) for two arbitrary countries. (You can find a full listing of country names that are accepted as valid in the bottom section of the same web page where the relevant WSDL document is listed). To test its operations, copy the URL pointing to the WSDL (http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl) to the Server URL setting of the HTTP Connection Manager within its Editor window in our sample package (you might want to take advantage of the "Test Connection" button to ensure that the target is reachable). Next, activate the Web Service Task Editor window, set its HttpConnection to the name of the just configured connection manager, specify a path to a local file where WSDL content will be stored, and click on the "Download WSDL" command button to initiate the download. After you have received confirmation that the operation completed successfully, switch to the Input section, select CurrencyExchangeService in the Service listbox and choose getRate as its Method. This will trigger a display of a two-row grid, with country1 and country2 input parameters of string data type as its entries. Locate any two valid country names from the Web Service web page and enter them in the Value column. Alternatively, you can create two variables of string type (in the Variables window, which you can activate from the SSIS menu), called, for example country1 and country2 and assign their values dynamically prior to invocation of the Web Service task. Once the variables are defined, mark the checkbox in the Variable column of the Input section in the Web Service Task Editor window and select an appropriate variable in the Value column (which becomes a listbox, displaying all available user and system variables). Switch to the Output section, choose File Connection as the OutputType and create a new File Connection Manager pointing to an XML file that will contain execution results. Finally, close the Web Service Task Editor window and execute the package. This should populate the target XML file with content resembling the following (illustrating in this case a declining ratio of American dollar to Polish zloty):

<?xml version="1.0" encoding="utf-16"?>

In the same, straightforward fashion, by simply supplying values of required input parameters, you can take advantage of any other Web Service method, regardless of the complexity of its implementation.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM