The omnipresent nature of Web-based technologies expanded the concept of distributed environments with the ability to utilize services hosted on remote servers accessible via standard Internet protocols. This capability can be easily leveraged in SQL Server 2008 R2 Integration Services by employing Web Services Task, whose characteristics have already been covered in detail on this forum. However, in some cases, it might be required to reference Web Services directly from within a Script Task code. We will describe how such a need can be accommodated in this article.
Web Services operate as software components that encapsulate arbitrarily designed features and make them accessible by employing a range of platform independent standards, such as XML (eXtensible Markup Language, defining data format), SOAP (Simple Object Access Protocol, governing rules of information exchange), HTTP (Hypertext Transfer Protocol, dictating negotiation and transmission behavior), WSDL (Web Services Description Language, providing the ability to describe Web services using XML-based syntax that identify their interfaces, message types, transport protocols and addressing conventions), and UDDI (Universal Description, Discovery, and Integration, delivering directory of Web Services by implementing their publishing and lookup functionality).
The majority of Web Services take the form of simple, highly specialized applets handling such tasks as measurement and currency conversions, data validation, financial markets and weather updates, or geographical or Internet location lookups. However, regardless of the level of their complexity, the underlying principle remains the same. The implementation interface is presented in a platform-agnostic manner according to WSDL specifications, documented in the corresponding XML-formatted file (for more details about its structure, refer to the Understanding WSDL article in the MSDN Library) and readily available to any client authorized to use the service.
In order to demonstrate this mechanism, we will write a sample code that leverages a free service hosted by www.webservicex.net to display city, state, area code and time zone information corresponding to a U.S. Zip code you provide.
To accomplish this, launch Business Intelligence Development Studio and create a new project based on the Integration Services template. Drag the Script Task from Toolbar onto the Designer interface. With the newly generated task highlighted, open the Variables window, define a variable named sZIP of String data type and Script Task scope, and assign to it a valid ZIP code value. Use the context-sensitive menu of the Script Task to display its Editor dialog box. Designate Visual Basic .NET 2008 as the ScriptLanguage and add User::sZIP to ReadOnlyVariables. Click on Edit Script… command button to access the Visual Studio Tools for Applications 2.0 interface.
Referencing a Web Service from a managed code relies on a proxy class, which is responsible for mapping WSDL attributes to appropriate XML elements, encapsulating and relaying SOAP messages to the destination, and processing incoming responses. This approach provides a layer of abstraction, hiding specifics involved in the client-server communication by utilizing a local component, whose methods and properties can be accessed in the same manner as other .NET Framework objects. In earlier versions of SQL Server Integration Services (and their respective visual Studio renditions), creating such a class involved the use of wsdl.exe utility (included in .NET Software Development Kit). This is no longer required, as the equivalent functionality is available natively within Visual Studio Tools for Applications 2.0.
To take advantage of it, in the Project Explorer window, right click on the top node representing the current project and select Add Web Reference… entry from the context-sensitive menu (optionally, you can accomplish the same by using the Web References subnode). In the resulting dialog box, type in the URL of the Web Service (http://www.webservicex.net/uszip.asmx?WSDL in our case) and press Enter to display its description, including an overview of its methods. In our example, we will present the usage of GetInfoByZIP, but you can just as easily employ the others (GetInfoByAreaCode, GetInfoByCity, or GetInfoByState). Click Add Reference command button to incorporate the sample Web service as a proxy class into our project (to identify its name, which you will need to instantiate it, use the View in Object Browser option from its context sensitive menu in the Project Explorer window).
Under some circumstances, you might run into the problem described in the Microsoft Knowledge Base article KB 955715. If this is the case, display the Properties interface of our Visual Studio Tools for Applications 2.0 project (by using its context sensitive menu in the Project Explorer window, switch to its Compile tab, click on Advanced Complile Options… command button, and change the Generate serialization assemblies listbox entry to Off (this will trigger reopening of the project, so make sure to save your work). Finally, add the following code to the body of Sub Main() procedure (where ST_<GUID>.vbproj.net.webservicex.www.USZip represents the autogenerated name of the proxy class, appearing in the Object Browser window):
Public Sub Main()
Try
Dim sZIP As String = Dts.Variables("sZIP").Value.ToString
Dim USZip As ST_<GUID>.vbproj.net.webservicex.www.USZip = _
New ST_ST_<GUID>.vbproj.net.webservicex.www.USZip
Dim AddressInfo As String = ""
For i As Integer = 1 To USZip.GetInfoByZIP(sZIP).FirstChild.ChildNodes.Count - 1
AddressInfo += USZip.GetInfoByZIP(sZIP).FirstChild.ChildNodes(i).Name + " " + _
USZip.GetInfoByZIP(sZIP).FirstChild.ChildNodes(i).InnerText + vbNewLine
Next
MessageBox.Show(AddressInfo, "Address Information")
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Exception")
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
As you can easily determine based on the code above, we are utilizing built-in methods of our proxy class in order to process the content of the XML-formatted response from the Web Service, whose sample content is presented below
<Table>
<CITY>New York</CITY>
<STATE>NY</STATE>
<ZIP>10104</ZIP>
<AREA_CODE>212</AREA_CODE>
<TIME_ZONE>E</TIME_ZONE>
</Table>
