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 = "https://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 https://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 https://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   https://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 ;)

Comments

• Anonymous
  April 22, 2010
  Thanks for the post !! I have SSIS 2008 and I did all the above using the web reference. I have a question. My data is being returned in a dataset from my webservice but I need to be able to write the data to multiple databases. I don't want to write it to an xml file. How do I write the data from the dataset directly to a database ? Is my only way to write more C# code and use insert statements ? I was hoping there is another way. Thanks !!

• Anonymous
  May 15, 2010
  Hi Greg_10, Please check "Multicast" transformation (ref: http://msdn.microsoft.com/en-us/library/ms137701.aspx) . It will help you multicast one dataset into many. "Multicast" transformation is available since SQL Server 2005. But it is more powerful with SQL Server 2008 . Check http://www.sqlis.com/post/Multicast-Transform-and-Threading-in-SQL-Server-2008.aspx for the details.

• Anonymous
  July 24, 2011
  Faruk, thanks for this script.  It's working perfectly in my development and QA environment. But when I move to production, if the web service process takes longer than 10 minutes, it never returns. I even tried the aync method created by the WSDL.exe and loop until the Completed event is fired. Once again, works perfectly in development and QA, but fails in Production. I've checked IIS settings in all 3 environments and don't see any differences. I've set the Tmeout property to 2 hours. FYI - Using SQL 2005. Any thoughts? thanks, Cheryl

• Anonymous
  August 23, 2011
  Hi Cheryl, I will suggest you to focus on why "The web service process takes longer than 10 minutes". Please check Tess's blog post blogs.msdn.com/.../asp-net-performance-case-study-web-service-calls-taking-forever.aspx Actually in this post, you will see a memory dump analysis part. But please read the problem and then you may omit the dump analysis part and jump to explanation for MaxConnection property under System.Net . Mosst probably you're hitting to the default limit "2" MaxConnection in your production environment but not in dev and QA environments.

• Anonymous
  May 10, 2012
  Thanks for this script. The www.mywetherinfo.com/myWebService.asmx is not working So i tried this example with another WSDL, the doubt is, can you provide the myWebService.vb code  that you used in this example cause im having problems with setting the proxy object to the webservice object's proxy property        svc.Proxy = myProxy This is to compare the result of the new generated class from the new WSDL with the one on your example.

• Anonymous
  August 20, 2014
  Hi this is great.. I am stuck however on the instantiate part.. I am using an existing webservice and i have added a service reference called - MBSDKServiceLD and a Web Reference called LANDeskMBSDK. these are resolving methods ok in VS 2013 - i just cannot get the code to auth to use the web services. Do i still need to run the WDSL against the asmx URL of my existing website?

• Anonymous
  October 26, 2014
  Thank you so Much..  it's been 2 days.. finally

• Anonymous
  November 17, 2016
  How to add the contents of myWebService.vb file into my script task ? If you can provide some details will be great