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

By:   |   Comments (4)   |   Related: More > Integration Services Development


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.


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:

unknown error

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:

could not read wsdl

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:

windows authentication

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

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;
		//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.

control flow

"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.

confguration properties
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Diana Moldovan 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

Comments For This Article

Thursday, March 15, 2018 - 6:56:24 AM - Diana Moldovan Back To Top (75422)

Hi, Mark, and pleae forgive me for being so late. I'm afraid that the project contains data which is sensitive for my customer and striping off these data will very much reduce any significance; therefore I can't send it as it is.  But otherwise I'm ready to help you as much as I can.



Thursday, March 8, 2018 - 12:23:09 AM - Mark Back To Top (75368)

 Your tip is very helpful for my current project.  Thanks a lot!  Is it possible to get a copy of your SSIS project with source code?


Monday, June 12, 2017 - 3:58:28 PM - Diana Moldovan Back To Top (57268)

Hi, Anveh, sorry for being so late...

Please see the code example from above:

//here I reference the web service				
ProxyClass.InventoryService invService = null;



Tuesday, May 23, 2017 - 9:37:37 AM - Anvesh Back To Top (55978)



I am working on a similar POC. Could you please clearly explain the below part how did you add the reference to the web service?

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.


Appreciate your help !!


get free sql tips
agree to terms