Consuming Web Services in SSIS Script Task

  • Article

In this blog I will discuss about how to consume a Web Service in the Script Task of a SSIS package.

To consume web methods in SSIS script task you need to follow the following steps:

 

Create a Proxy Class of a Published Web Service

In order to consume the Web Service in SSIS Script Task, first you need to create a Proxy Class by using WSDL

Here is how you can create a Proxy Class using WSDL from .NET command prompt:

wsdl /language:VB https://localhost:8080/WebService/Service1.asmx?WSDL /out:C:\WebService1.vb

Syntax : wsdl /language:<VB/CS/JS/VJS/CPP> <Web Service URL>?WSDL /out:C:\<WebServiceProxyClassName>.vb

 

By default it uses 'CS' (C Sharp), if you don’t specify the language with language switch.

Please note thatIn SSIS Script Task2005 you can only use Visual Basic .NET as script language, so generate the Proxy Class with VB if you are using SSIS 2005.

 

For those who are not very familiar with WSDL.EXE , This is aUtility to generate code for XML Web Services and XML Web Service clients using ASP.NET from WSDL contract files, XSD schemas and .discomap discovery documents. This tool can be used in conjunction with disco.exe. (Ref : https://msdn.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx)

 

How to Publish a Web Service to IIS

Right Click on the Web Service Project and Select "Publish". It will show you the Publish Web dialog box, There you need to select the Web Site where you wish to publish the Web Service.

image

 

 

Add Proxy Class to the Integration Services Project Script Task :

Here are the steps to consume a Web Services method in Script task:

 

1. Open package in Integration Services Project

2. Drag and drop the Script task

3. Set "PrecompileScriptIntoBinaryCode =False" (Right-Click on script task -> Edit -> Select "Script" )

4. Right-Click on script task -> Edit -> Select "Script" -> click on "Design Script.." then you will get Script Editor open.

image

 

5. Open the Project Explorer (View -> Project Explorer), if this doesn't show the Explorer window, close the script editor and reopen it (Step 2)

6. Add the VB proxy class to the project (Right-Click -> Add Existing Item… -> then add the <.VB> class in the project. Build the code (Debug -> build).

7. Make sure that we include the "System.Web.Services" and "System.Xml.Serialization" namespaces ("Imports <namespace>") in the “WebService1.vb” class.

a. In the script editor, from the Project menu, use "Add Existing Item.. " and add the proxy class. You may see some "garbage" characters in very first line of the Proxy class, which are the Unicode byte order mark, delete the first line with garbage characters.

b. Add reference of "System.Xml" and "System.Web.Services" assemblies.

8. Now in "ScriptMain" class create object of the proxy class and call the web services methods.

 

Dim ws As NewService1

MsgBox("Square of 2 : "& ws.Square(2))

Dts.TaskResult = Dts.Results.Success

 

You may sometime get a similar error while accessing the Web Services in the Script Task:

DTS Script Task has encountered an exception in user code:

Project Name: ScriptTask_ae9238d3988b45c184a84c8f4e66ddb6

The request failed with HTTP status 401: Unauthorized.   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ScriptTask_ae9238d3988b45c184a84c8f4e66ddb6.Service1.Square() in dts://Scripts/ScriptTask_ae9238d3988b45c184a84c8f4e66ddb6/WebService1:line 81 at ScriptTask_ae9238d3988b45c184a84c8f4e66ddb6.ScriptMain.Main() in dts://Scripts/ScriptTask_ae9238d3988b45c184a84c8f4e66ddb6/ScriptMain:line 32

 

To resolve the issue Modify the code and add Credentials before calling the web method:

Public SubMain()

Dim ws As NewService1

ws.Credentials = New System.Net.NetworkCredential("user name", "password", "domain name")

MsgBox("Square of 2 = "& ws.Square(2))

Dts.TaskResult = Dts.Results.Success

End Sub

 

Hope this would help when you need to consume web services within script task of integration Services.

Author : Praveen(MSFT) , SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep (MSFT) , SQL Developer Engineer, Microsoft