Consume Webservice via SSIS Script Component

I have always believed SSIS Script component is a brilliant task and it provides for accessing data from any type of source. I have used it as a SOURCE Component very recently to consume data from Webservice, and it required a bit of a tweak to overcome the limitations on this Script component. viz:

Script Component doesn’t support reading data from a Config file, you can have a look at the problem i had published here

The scenario i will explain below, would consume a WebService which is a WCF service targeted at Framework 3.5, and use HTTPBINDING.

So how to we proceed step wise as:

1. Create a DFT and drop a DFT task.

2. Inside the DFT place the Script Component, select the SOURCE


3) Now open the Script component to edit the Script, look for Add Service Reference on the Reference section if you dont see it change the project to target Framework 3.5, once done you would be able to see the AddSerivceRefrence


4. Adding the Service Reference would lead to adding a file a App.config on the project, which would have the bindings and Contract settings for the web service, Genrally you would use this and from code call the settings stored on this file to access the Webservices.



Resolution is to define the bindings at runtime so use a code like below, wherein you define the BINDINGS via code rather then depending on the App.config file.

You would need to use the code below for BASICHTTPBINDING

BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportWithMessageCredential);
binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
binding.MaxBufferSize = 2147483647;
binding.MaxBufferPoolSize = 2147483647;
binding.MaxReceivedMessageSize = 2147483647;

// Replace <machinename> with the name of the development server.
EndpointAddress remoteAddress = new EndpointAddress(“<service Link>”);

ReportPortClient client = new ReportPortClient(binding, remoteAddress);
client.ClientCredentials.UserName.UserName = “USER NAME”;
client.ClientCredentials.UserName.Password = “Password”;

BindingElementCollection elements = client.Endpoint.Binding.CreateBindingElements();
elements.Find<SecurityBindingElement>().IncludeTimestamp = false;
client.Endpoint.Binding = new CustomBinding(elements);

// THis portion is specific to my call of web service

Execute_ReportType request = new Execute_ReportType();
response = client.Execute_Report(request);

For NETTCPBINDING and defining the bindings from code you could have a look at:

Reach out to me at my Email if you some clarifications and require assistance in consuming a Webservice via the Script Component task,


PS: Ensure for the SSIS Script component Source to work properly the SSIS package executing account must have read/ write permissions on the TEMP(generally- C:\Windows\Temp) directory, on the box the package would execute



2 Responses to Consume Webservice via SSIS Script Component

  1. Deângelo says:

    Works Fine, thanks so much!

  2. I am truly thankful to the holder of this website who has shared this great paragraph at at this time.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: