Web Services

This has got to be the one of the coolest tools in the SSIS bag of tricks. You barely need to know what a Web service is to take advantage of this little gem.

In very simple terms, a Web service is to the Web as a function is to a code module. It accepts a message in XML, including arguments, and returns the answer in XML. The wonderful thing about XML technology is that it allows computer systems that are completely foreign to each other to communicate in this common language. When using Web services, this transfer of XML data occurs across the enterprise or across the Internet using the HTTP protocol. Many Web services — for example, stock-tickers and movie listings — are freely available for anyone's use. Some Web services, of course, are private or require a fee. Probably the most useful application is to allow orders or other data to be exchanged easily by trading partners. In this example, you'll learn how to use a Web service to get a listing of the hyperlinks on a Web page.

The Hyperlink Extractor Service

Start with a new project and create an HTTP Connection by right-clicking in the Connection Managers pane and choosing New Connection. Choose HTTP and click Add to bring up the HTTP Connection Manager Editor. Type http://www.atomic-x.com/xmlservices/HyperlinkExtractor.asmx?wsdl as the Server URL (see Figure 8-26). In this case, you'll use a publicly available Web service so you won't have to worry about any credentials or certificates. If you must supply proxy information to browse the Web, fill that in on the Proxy tab. Go ahead and test the connection before continuing, and then click OK to accept the Connection Manager.

Figure 8-26

Add a Web Service task from the Toolbox. Double-click it to bring up the Web Service Task Editor. Select the General pane. Make sure that the HttpConnection property is set to the HTTP connection you created in the last step.

In order for a Web service to be accessed by a client, a Web Service Definition Language (WSDL) file must be available that describes how the Web service works — that is, the methods available and the parameters that the Web service expects. The Web Service task provides a way to automatically download this file. Enter the fully qualified path where the file will be created (see Figure 8-27). If the file already exists, set OverwriteWSDLFile to true. Click Download WSDL to create the file. If you are interested, you can open the file with Internet Explorer to learn more about its structure.

Figure 8-27

By downloading the WSDL file, the Web Service task now knows the Web service definition. Select the Input pane of the Web Service Task Editor. Select HyperLinkExtractor next to Service. Next to Method, select ExtractURL.

Note

Web services are not limited to providing just one method. If multiple methods are provided, you'll see all of them listed.

Once the Method is selected, a list of arguments appears. In this case, enter a URL for your favorite Web page, for example www.sqlservercentral.com (see Figure 8-28).

Figure 8-28

Now that everything is set up to invoke the Web service, you need to tell the Web Service task what to do with the result. Select Output in the left pane. You can store the results in a variable or in a file. For this example, choose File Connection. Next to the File property, click the drop arrow and choose <New Connection>. The File Connection Manager Editor opens. Next to Usage Type, select Create File. Type in a path for your new file, and click OK (see Figure 8-29). Click OK once more to accept the Web Service task configuration.

Figure 8-29

Now you're ready to run the project. If all went well, you can now open the XML file that was returned by the Web service and see a list of all the links on the page of the URL you entered previously.

The Currency Conversion Service

In this second example, you'll learn how to use a Web service to get back a value that can be used in the package to perform a calculation. You'll use the value with a very cool Data Flow Transformation component, the Derived Column, to convert a price list to another currency.

Begin by creating a new SSIS package. This example will require three variables. To set up the variables, make sure that the Control Flow tab is selected. If the variables window is not visible, right-click in the design area and select Variables. Set up the three variables as in the following table.

Open table as spreadsheet

Name	Scope	Data Type
XMLAnswer	Package	String
Answer	Package	String
ConversionRate	Package	Double

Add a Connection Manager pointing to the AdventureWorks database. Add an HTTP Connection Manager and set the Server URL to http://www.webservicex.net/CurrencyConvertor.asmx?wsdl.

Note

Note that this Web service was valid at the time of this writing, but the authors cannot guarantee its future availability.

Drag a Web Service task to the design area. Double-click the task to open the Web Service Task Editor. Set the HTTPConnection property to the Connection Manager you just created. Download the WSDL file as you did in the last example.

Click Input to see the Web service properties. Select CurrencyConvertor in the Service property and ConversionRate as the Method. Two parameters will display, FromCurrency and ToCurrency. Set FromCurrency equal to USD and ToCurrency equal to EUR (see Figure 8-30).

Figure 8-30

Click Output and set the OutputType to Variable. The variable name to use is User::XMLAnswer. Click OK to accept the configuration.

Note

At this point, you may be interested in viewing the XML that it returned from the Web service. You can save the XML in a file instead of a variable. Then, after running the task, examine the file. Or, you can set a breakpoint on the task and view the variable at runtime. See Chapter 13 to learn more about breakpoints and debugging.

The value of the XML returned will look something like this:

 <?xml version="1.0" encoding="utf-8"> <double>0.836</double> 

You just need the number, not the XML, so add an XML task to the designer to evaluate the XML. Drag the Precedence Constraint from the Web Service task to the XML task. Open the XML Task Editor by double-clicking the XML task and change the OperationType to XPATH. The properties available will change to include those specific for the XPATH operation. Set the properties to match those in the following table.

Section	Property	Value
Input	OperationType SourceType Source	XPATH Variable User:XMLAnswer
Output	SaveOperationResult	True
Operation Result	OverwriteDestination Destination DestinationType	True User::Answer Variable
Second Operand	SecondOperandType SecondOperand	Direct Input /
Xpath Options	PutResultInOneNode XpathOperation	False Values

The XPATH query language is beyond the scope of this book and, luckily, this XML is very simple with only a root element that can be accessed by using the slash character (/). Values are returned from the query as a list with a one-character unprintable row delimiter. In this case, only one value is returned, but it still has the row delimiter that you can't use.

There are a couple of options here. You could save the value to a file, then import using a File Source component into a SQL Server table, and finally use the Execute SQL task to assign the value to a variable. But, in this example, you will get a chance to use the Script task to eliminate the extra character.

Add a Script task to the design area and drag the Precedence Constraint from the XML task to the Script task. Open the Script Task Editor and select the Script pane. In order for the Script task to access the package variables, they must be listed in the ReadOnlyVariables and ReadWriteVariables properties in a semicolon-delimited list. Enter User::Answer in the ReadOnlyVariable property and User::ConversionRate in the ReadWriteVariables property (see Figure 8-31).

Figure 8-31

Click Design Script to open the code window. A Microsoft Visual Studio For Applications environment opens. With Intellisense and color-coding, the Script task bears little resemblance to the old ActiveX Script task from SQL 2000 DTS. The script will save the value returned from the Web service call to a variable. One character will be removed from the end of the value, leaving only the conversion factor. This will then be converted to a double and saved in the ConversionRate variable for use in a later step. Replace Sub Main with the following code:

 Public Sub Main()     Dim strConversion As String     strConversion = Dts.Variables("User::Answer").Value.ToString     strConversion.Remove(strConversion.Length -1,1)     Dts.Variables("User::ConversionRate").Value = CType(strConversion,Double)     Dts.TaskResult = Dts.Results.Success End Sub 

Close the scripting environment, and then click OK to accept the Script task configuration. Add a Data Flow task to the design area and connect the Script task to the Data Flow task. The Control Flow area should resemble what you see in Figure 8-32.

Figure 8-32

Move to the Data Flow tab. Add a Connection Manager pointing to the AdventureWorks database if you did not do so when getting started with this example. Drag an OLE DB Source component to the design area. Open the OLE DB Source Editor and set the OLE DB Connection Manager property to the AdventureWorks connection. Change the Data Access Mode property to SQL command. Type the following query in the command window:

 SELECT ProductID, ListPrice FROM Production.Product WHERE ListPrice > 0 

Click OK to accept the properties. Add a Derived Column transform to the design area. Drag the Data Flow Path from the OLE DB Source to the Derived Column component. Double-click to open the Derived Column Transformation Editor dialog box. Variables, columns, and functions are available for easily building an expression. Add a Derived column called EuroListPrice. In the Expression field type:

 ListPrice * @[User::ConversionRate] 

The Data Type should be decimal with a scale of 2. Click OK to accept the properties (see Figure 8-33).

Figure 8-33

Add a Flat File Destination component to the Data Flow design area. Drag the Data Flow Path from the Derived Column component to the Flat File Destination component. Bring up the Flat File Destination Editor and click New to open the Flat File Format dialog. Choose Delimited and click OK (see Figure 8-34).

Figure 8-34

The Flat File Connection Manager Editor will open. Browse to or type in the path to a file. Here you can modify the file format and other properties if required (see Figure 8-35). Check Column Names in the First Data Row.

Figure 8-35

Click OK to dismiss the Flat File Connection Manager Editor dialog box. You should now be back at the Flat File Destination Editor. Click Mappings and then click OK. The Data Flow design area should resemble what you see in Figure 8-36.

Figure 8-36

Run the package and then open the file that you defined in the last step. You should see a list of products along with the list price and the list price converted to Euros (see Figure 8-37).

Figure 8-37

Many free Web services are available for you to try. See www.xmethods.net for a list of services, some of which are free. In the next section, you will learn how to import an XML file into relational tables.