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"?> <float?>2.8479</float?>
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.
