Download the data from web service with Authentication by C# in SSIS

SSIS package exclusively gives web service task to download the data from web service directly. But some of the custom level or encrypted web service would not allow to download using web service task. So we need to choose script task to implement the custom code to download the data from web service and manage the custom encryption in code level.

How to download data from web service using Script task

Primary steps in SSIS package

• Create Integration Service project in Visual studio.
• Declare the set of variables for web service URL, Authentication details and other encryption key details.
• Drag and drop the Script Task on Control flow, edit the Script Task and define the variables in read only variables which we were created in variables declaration.

Step 1: In Script Task, go to Solution Explorer in right side and right click on References then click Add Service Reference from the list.

Step 2: Define the Web Service URL on address text box then click Go button and you can receive service response along with list of operations. Define the name for the service on Name space text box. For instance, I'm defining the name as "BIDataService".

Step 3: You need to include below mentioned name space in your C#/VB.NET code like as below. Here ST_9940a5fc3d1649e1a5e0575281ceb75a is a project name of Script Task which you can get it in Script Task's Solution Explorer window. 

using ST_9940a5fc3d1649e1a5e0575281ceb75a.BIDataService;
using System.ServiceModel;

Step 4: Create method like as below to establish the connection and setting for the web service.

private static BIDataServiceClient CreateBIDataService(string WebServiceURL)
{
 // create an endpoint address using the URL
 EndpointAddress endptAddress = new EndpointAddress(WebServiceURL);

 // create and configure the WS Http binding
 WSHttpBinding wsBinding = new WSHttpBinding();
 wsBinding.Security.Mode = SecurityMode.Transport;
 wsBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
 wsBinding.SendTimeout = TimeSpan.FromMinutes(10);
 wsBinding.OpenTimeout = TimeSpan.FromMinutes(10);
 wsBinding.CloseTimeout = TimeSpan.FromMinutes(10);
 wsBinding.ReceiveTimeout = TimeSpan.FromMinutes(10);
 wsBinding.BypassProxyOnLocal = false;
 wsBinding.TransactionFlow = false;
 wsBinding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
 wsBinding.MaxBufferPoolSize = 524288;
 wsBinding.MaxReceivedMessageSize = 2147483647;
 wsBinding.MessageEncoding = WSMessageEncoding.Text;
 wsBinding.TextEncoding = System.Text.Encoding.UTF8;
 wsBinding.UseDefaultWebProxy = true;
 wsBinding.AllowCookies = false;
 // create and return the client proxy
 return new BIDataServiceClient(wsBinding, endptAddress);
}

Step 5: Call above method on your C# code like as below.

BIDataServiceClient biClient = CreateBIDataService(BIDataService);
biClient....
..............Your Code....
........

See Also:

Dynamic SQL Query with OLEDB Command in SSIS Package
Implement Best Practice in SSIS ETL Development

Don't forget to share this