How to: Call a Web Service by Using the Web Service Task (SQL Server Video)

Video Summary

Learn how to call a Web service successfully from an Integration Services package by configuring an HTTP connection manager and the Web Service task. Then, set up an XML task to read the information that you have retrieved, and use that information in the package.

Video Transcript

Hello. My name is Douglas Laudenschlager, and I work on the documentation team for Microsoft SQL Server Integration Services.

Today, you're going to learn how to call a Web Service by using the Web Service task.

You'll also learn how to:

• Configure an HTTP connection manager.

• Configure the Web Service task itself.

• Download the WSDL file that describes the Web service.

• Call a Web method and supply the expected input values.

• Read the return value by using an XML task.

• And, use the results from the Web service in the package.

Here we are in Business Intelligence Development Studio, where I've already created an Integration Services project, opened the package, and added some of the components that we need for our demonstration.

Before we work on our package, let's take a look at the Web service that we're going to use. This is a free public Web service that performs currency conversion. While we're here, let's copy the URL to the clipboard for use in our package. This Web service exposes a single Web method named ConversionRate that returns conversion rates for most of the world's currencies. When we test it by asking for the conversation rate from US dollars to European Euros, the XML response that comes back is the same response that our package will receive.

Let's go back to our package. First, we need to create some package variables. We need a variable to hold the XML response from the Web service, which we'll call ConversionRateResponse, and a second variable to hold the conversion rate itself, which we'll call ConversionRate. Both of these are string values.

The next prerequisite for the Web service task is an HTTP connection manager that contains the URL for the Web service. Let's add a new HTTP connection here, and paste in the URL of the Web service. Since we're going to need to download the Web Services Description Language file, or WSDL file, for the Web service, we'll add the ?wsdl at the end of the URL. Now, let's save our connection manager...

...and we're ready to add the Web Service task itself. When we open the Web Service Task Editor, the first thing we have to do is assign the HTTP connection manager that we just created. Now, we need to download the WSDL file that describes what the Web service can do...but it's a quirk of the Web Service task that the Download WSDL button isn't enabled until you've selected a local file on your computer. So, we're going to satisfy this requirement by creating an empty text document and naming it CurrencyConvertor.wsdl. Then, we select the dummy file that we just created, change the value of OverwriteWSDLFile from False to True, and now we can successfully download the WSDL file.

Now, let's move to the Input page of the editor. Thanks to the WSDL file, the task knows all about the Web service. We can select the Web service, select its ConversionRate Web method, and configure the Web method call to request the conversion rate from US dollars to European Euros.

Now, let's move to the Output page of the editor. We want to save the output to a variable, not a file—to the variable that we named ConversionRateResponse. Now, let's save our Web Service task.

The response that our task is going to receive is the XML document that we saw when we tested the Web service online. From this, we need to extract the conversion rate itself. For this, we'll use an XML task.

The XML task can do a lot of different things with XML documents, so it has a lot of properties to configure. The first thing we have to select, although it's not the first item on the page, is the operation that we want to perform—we want to perform an XPATH query against the XML. Our XML source is in the variable that we named ConversionRateResponse. We want to save the result of our query, we want to save it to a variable, to the variable that we named ConversionRate, and we want to overwrite any existing value with the current value. We're going to type in our XPATH query directly, and query for the "/double" node that contains the conversion rate. This is an XPATH operation to extract Values. Now, let's save our XML task.

I want to show you a useful technique for checking the values of package variables as your package runs. So, let's set a breakpoint on the OnPostExecute event of the XML task, and run our package.

[pause]

Now, the package has paused at the breakpoint we set, and we can come down here to the Locals window and see the current value of any package variable. If we scroll down the list, we find ConversionRateResponse with the long XML string, and ConversionRate with just the rate itself. If we'd like to see these two in a less cluttered list, we can use the Add Watch command to add the two of them to the separate Watch window.

Now, let's run our task to completion, stop debugging, and remove the breakpoint that we set.

Next, we're ready to hook up and re-enable the Data Flow task that I've already started to set up.

First, we're going to use an OLE DB source to extract data from a view in the AdventureWorks sample database. We're going to get the LastName for each salesperson, and their ytd sales in US dollars.

Then, we're going to use the conversion rate that we got from the Web service to add a NEW column that converts the ytd sales into European Euros. To add a new column, we use a Derived Column transformation. We'll name our new column SalesYTDEuro, and define it as the values of the SalesYTD column times the conversion rate that we obtained from the Web service. First, we have to cast the conversion rate, which is a string, to an appropriate numeric data type. Then, let's ROUND the result neatly to two decimal places, and save our Derived Column transformation.

We don't want to bother saving our sample data, so we're going to use a Row Count transformation to terminate the data flow. This is a handy shortcut, and the only requirement is that you have to assign the row count value to a package variable, which you can ignore.

Now, let's add a Data Viewer to our data flow so we can see the results of our work as the package runs. We'll accept the default Grid format, and run our finished package.

[pause]

After a few moments, we see that everything has worked as expected, and our data now contains a new column with ytd sales converted from dollars to Euros, thanks to the conversion rate that we obtained from the Web service by using the Web service task and the XML task.

In this video, you learned how to call a Web Service by using the Web Service task.

You also learned how to:

• Configure an HTTP connection manager.

• Configure the Web Service task itself.

• Download the WSDL file that describes the Web service.

• Call a Web method and supply the expected input values.

• Read the return value by using an XML task.

• And, use the results from the Web service in the package.

We hope that you've learned new things and useful skills from this video. After you close this video and return to the Web page, you'll find some other Integration Services videos that are available for you to watch. Thank you.