Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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:

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

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.


Last Update:


next webcast button


next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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 08, 2018 - 12:23:09 AM - Mark Back To Top

 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

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

 Hi,

 

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 !!

 


Learn more about SQL Server tools