SSIS 2005: Consuming a Web Service within a Script Task (without using Web Service Task or an HTTP Connection Manager)

When you read the title, I’m pretty sure that you asked “Why do I need to do this web service consuming things in a Script Task although I can use ready-to-use Web Service Task ?”.

Well… I faced issues for some scenarios that you will need to consume the web service in a Script Task as a workaround. For a customer scenario, we had issues with this scenario (WebService Task + HTTP Connection Manager + Proxy Server with NTLM Authentication) and we fixed their issue doing all the things in a Script Task.

As an SSIS Developer, “Script Task” is there for nearly all your needs as .NET is there waiting for you. If you can’t do something with the built-in SSIS tasks, try to do the same functionality inside a Script Task.

As “Script Task” is a “Task” already, you can take the input from SSIS Variables or the Input Columns you attached to the input of the Script Task. You can send data out of Script Task again to SSIS Variables or the Output Column you will define.

Let’s go back to the web service consuming scenario …

Here’s the Script Task code that I used to consume a web service in a Script Task :

Public Sub Main()

        Dim strProxyURL As String = “http://www.yourproxyurl.com:NNNN“

        Dim strProxyUsername As String = “myusername”

        Dim strProxyPassword As String = “mypassword”

        Dim strProxyDomain As String = “MYDOMAIN”

        Dim myProxy As WebProxy = New WebProxy(strProxyURL, True) ‘ //bypass on local = true

        myProxy.Credentials = New NetworkCredential(strProxyUsername, strProxyPassword, strProxyDomain)

        ‘instantiate a web service object

        Dim svc As New myWebService()

        ‘set our proxy object to the webservice object’s proxy property

        svc.Proxy = myProxy

        ‘create a DataSet to have the result from the method

        Dim ds As DataSet = svc.WeatherInfo(“Istanbul”, “Turkey”)

        ‘The 1st DataTable has the output. Write this XML content to a text file

        ds.Tables(0).WriteXml(“C:\\WebServiceResults.xml”)

        Dts.TaskResult = Dts.Results.Success

      End Sub

Here in this script task, I’m not taking anything from the “flow” or SSIS Variables. It works  and writes the result to an XML file.

I want to underline an important point about namespaces. By default, the references below are added to a Script Task :

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

But we will need to add the references  below also :

Imports System.Net

Imports System.IO

Imports System.Text

Imports System.ComponentModel

Imports System.Diagnostics

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Xml.Serialization

Imports System.Xml

Let’s go over the script. First of all, I’m defining a couple of string variables to build up a WebProxy class to use it with the web service proxy class. Then the tricky line comes in :

       ‘instantiate a web service object

       Dim svc As New myWebService()

This is the tricky part of this implementation. As you can see I’m instantiating a “myWebService()” object. This “myWebServcice” is a class that I defined below in the Script Task code. But I did not wrote this class by hand. I used our “wsdl.exe” tool which is coming with .NET Framework. This tool generated code for web service clients from WSDL file. You can refer to http://msdn.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx for details.

I used the line below to generate the VB.Net code :

               Wsdl.exe /l:VB /n:myWebService /out:myWebService.vb http://www.mywetherinfo.com/myWebService.asmx?WSDL

This command created the myWebService.vb file with VB.Net code for the web service defined in the WSDL URL  http://www.mywetherinfo.com/myWebService.asmx?WSDL

Then I added the contents of the myWebService.vb file into my script task. That’s it 🙂

As I said, this might not be useful for all scenarios. I think that I gave an example for the power of Script Task at least 🙂

P.S. : Luckily the “Add Web Reference” feature is available in SSIS 2008 Script Task. So you won’t need to do all those things in SSIS 2008. As you do in the way you used to do in your WinForms, WebForms apps in your other Visual Studio 2008 Projects; just add your web reference and use it in your Script Task 😉