Using a Script Task in SQL Server Integration Services SSIS to Connect to a Web Service

By: Diana Moldovan   |   Updated: 2016-05-05   |   Comments (4)   |   Related: More > Integration Services Development

Problem

Recently one of my customers asked me to insert data into their medical equipment inventory database using a web service. I decided to use SSIS to do this, but this task was not as easy as I initially thought. In this tip I'll describe the problems I encountered and how I dealt with them.

Solution

The best way to fulfill this task is to "break" it in a sequence of steps, for example:

• Collecting the data from the web service
• Inserting this data in staging tables
• "Merging" the data from the staging tables with the data from the production tables. New data will be inserted "in production". If the existing data changed, then the current row will be updated.

Because of this sequential structure, I've decided that SSIS is a good implementation option. I felt quite comfortable to "translate" the sequence of steps in SSIS components and to rely on its robust error logging. Furthermore, for the first step I planned to use the Web Service Task component.

SSIS Web Service Task Issues

I had already installed Visual Studio 2015 on my dev machine. In order to be able to work on an Integration Services project, I installed the 2015 version of SQL Server Data Tools.

Following Daniel Calbimonte's instructions I opened a new Integration Services project in Visual Studio and dragged a Web Service Task onto the Design pane. I configured the HTTP connection manager using the web service wsdl link. Since the site required credentials, I ticked the "Use credentials" checkbox and added the user name and password. I clicked on the "Test Connection" button and the connection succeeded. However, when I tried to download the wsdl file I received a rather cryptic error message:

According to the Integration Services error reference, "Exception from HRESULT 0xC001600D" stands for "unknown status code".

When I moved to the "Input" tab, I encountered another error. In fact I expected this, since the wsdl was not downloaded:

After a few attempts I realized that the web service asks for Windows Authentication, which is not supported by the HTTP Connection Manager. This was clear when I tried to access the web service in Internet Explorer - notice the "Windows Security" box title:

SSIS Web Service Task Workaround

Therefore I had to work around this limitation and connect to the web service from within a script task. Basically this script task reproduces the functionality of the web services task, i.e. it collects the product and manufacturer data from the web service in files on disk.

In a C# project I added a "service reference" to the web service, so the objects, properties and methods were exposed. For my Integration Services project I created a class containing these objects using wsdl.exe.

Wsdl /language:CS /n:ProxyClass /out:”C:\MyFolder\ProxyClass.cs” /u:user_name /p:password https://webservices.mysupplier.net/InventoryService/InventoryService.svc?singleWsdl

I added Proxyclass.cs to the project and referenced it in the script task code.

public void Main()
{
	//the web service credentials are stored as variables	
	String userID = Dts.Variables["btUser"].Value.ToString();
	String password = Dts.Variables["btPwd"].Value.ToString();
	.
	.
	.
	//here I reference the web service				
	ProxyClass.InventoryService invService = null;
	.
	.
	.
	try
	{
		.
		.
		.
		//here I create a new instance of the service
		invService = new ProxyClass.InventoryService();
		//here I use the credentials defined above
		invService.Credentials = new System.Net.NetworkCredential(userID, password);

		//code that retrieves the product and manufacturer data in files on disk
		.
		.
		..
		.
		..
		.
		.
	}
	catch(Exception e)
	{
		//Dts.Events.FireError logs the exception message in the error log. 
		Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "An error occurred: " + e.Message.ToString(), "", 0);
		Dts.TaskResult = (int)ScriptResults.Failure;
	}
}    

I "translated" the whole process in a straightforward control flow. If there is any error, everything stops and the error message is logged in a text file.

"RetrieveFromWebService" is the script task I've already talked about. The sequence container holds SQL tasks that truncate the staging tables and data flow tasks that import the data from flat files on disk into the staging tables after truncation. Finally, the data from the staging tables is merged with the data from the production tables.

The version of SSDT I used allowed me to compile the project targeting different versions of SQL Server. Therefore I've done the development work on my SQL 2014 SP1 machine and I could easily provide a SQL 2012 build for my customer.

Next Steps

• Explore other SSIS development tips here.
• Learn more about the script task, you’ll find out that the script task can be incredibly useful in many situations.

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips