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
http://bishtabhinav.files.wordpress.com/2013/04/untitled.gif?w=650
3) Now open the Script component to edit the Script, look for Add Service Reference on the Reference section if you don't see it change the project to target Framework 3.5, once done you would be able to see the AddSerivceRefrence
http://bishtabhinav.files.wordpress.com/2013/04/untitled1.gif?w=650
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, Generally you would use this and from code call the settings stored on this file to access the Webservices.
http://bishtabhinav.files.wordpress.com/2013/04/untitled2.gif?w=650
THIS IS THE PROBLEM WE CAN'T CALL THE WEBSERVICE NORMAL WAY AS SSIS COMPONENT TASK DOESN'T RECOGNISE THE APP.CONFIG file,
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>”);
<ExposedMethodObject> client = new <ExposedMethodObject>(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:
http://www.bibits.co/post/2012/08/15/Consuming-Web-Services-in-SSIS-Script-Component.aspx
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