Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Consuming web services in SSIS 2008

SQL Server Integration Services (SSIS) 2008 is bringing with it new capabilities. One of the biggest new features is the new scripting engine. Previously Visual Studio Tools for Applications (VSA) was used as the scripting engine but that has now been superceded by Visual Studio Tools for Applications (VSTA).

In the video below I demonstrate some of the capabilities of VSTA within SSIS 2008. Specifically, I show how easy it now is to work with web services from right inside the SSIS pipeline. The video may not show up in some blog readers in which case head here to watch it.


The video has lost a bit of fidelity when uploaded to Youtube due to them shrinking it. I tried to attach the original version to this blog entry but unfortunately our blogging engine (Community Server) rejected the file for being too large (its 5.5MB). If you would like a copy of the video emailing to you then please feel free to contact me here.    UPDATE: The video is now available for download from my Live Folders space here: http://folders.live.com/self.aspx/VQ9oHa1TJjc/Public/BlogShare/20070619/Consuming%20Web%20Services%20in%20SSIS%202008/Consuming%20Web%20Services%20in%20SSIS%202008v2.zip UPDATE: The Live Folders team have updated their service and lo and behold none of my links work anymore. Here's the new link:

Thanks to my colleague and drinking buddy Andy Britcliffe who helped me out with some of the code.

Attached to this blog entry is the package that I built while recording the video. You don't need to do any configuration in order to execute the package, you just need to have an internet conenction.


UPDATE: 2008-10-22 - I've uploaded a version of this demp package that will work on the final release of SQL Server 2008. See here: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20070619/Consuming%20Web%20Services%20in%20SSIS%202008/ConsumingWebServices%7C_RTM.zip

Published Tuesday, June 19, 2007 9:50 PM by jamie.thomson



Mohit Nayyar said:

Happy birthday Jamie..............

June 21, 2007 4:57 PM

SSIS Junkie said:

For those of you that don't read the same RSS feeds as me, Live Folders is now available as a limited

July 1, 2007 6:27 PM

Matt Low said:

Nice one mate, very cool stuff! Where DO you find the time to do all this?

- Nigel

July 11, 2007 6:12 PM

SSIS Junkie said:

I've spoken about Live Folders quite alot on here lately and some big news from that product team

August 9, 2007 7:00 PM

SSIS Junkie said:

A few days ago I wrote about Skydrive and was critical of the fact that existing links no longer worked

August 13, 2007 5:59 PM

David DeLoveh said:

Hey Jame.  I think you have a tpyo in this one.  You refer to VSA as

"Visual Studio Tools for Applications (VSA)" instead of "Visual Studio for Applications".  And as usual, excellent blog entry.

December 5, 2007 6:47 PM

jamie.thomson said:

Whoops. cheers David. Fixed now.

December 5, 2007 9:32 PM

SSIS Junkie said:

"Why the <insert your favourite expletive here> doesn't SSIS support C# for the script task and

March 4, 2008 4:35 PM

paul s said:

is there any chance to include the script code in notepad? Ths fails to load in SQL Server 2008 (RC0) with "error encountered upgrading to current version"

great blog btw :)

July 17, 2008 5:33 PM

paul s said:

ah, go tot he package, right click and "show code" - search under CreateNewOutputRows() hth

July 18, 2008 10:47 AM

Dave K said:

Once you search for CreateNewOutputRows(), then what?

July 20, 2008 1:39 AM

Nathan said:

This looks very similar to the manner in which many people had to work with WCF services in 2005...is this a particular improvement, sans the web reference bit vs adding your own proxy assembly? I haven't craked open 2008 yet...(setting up a VM now), but does the web service adapter still exist...and if so, has its WCF support improved?

August 21, 2008 8:59 AM

SSIS Junkie said:

Quick note. Today I updated the demo package that I built for my blog entry Consuming web services in

October 22, 2008 8:02 PM

rüya tabiri said:

Thank You...

November 26, 2008 5:53 PM

rüya tabirleri said:

Thank You...

November 26, 2008 5:54 PM

Developer hearted / Relational minded said:

Having stated the need for getting data from SSIS packages in Reporting Service in my last post (Part

May 12, 2009 7:39 AM

SSIS Junkie said:

Of late one may have noticed that I have become very interested in high-brow, generally vaporous, disciplines

June 23, 2009 11:16 PM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems