THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Five things SSIS should drop

There’s a current SQL Server meme going round entitled Five things SQL Server should drop and, whilst no-one tagged me to write anything, I couldn’t resist doing the same for SQL Server Integration Services. So, without further ado, here are five things that I think should be dropped from SSIS.

Data source connections
Seriously, does anyone use these? I know why they’re there. Someone sat in a meeting back in the early part of the last decade and said “Ooo, Reporting Services and Analysis Services have these things called Data Sources. If we used them in Integration Services then we’d have a really cool integration story.” Errr….no.

Web Service Task
Ditto. If you want to do anything useful against anything but the simplest of SOAP web services steer well clear of this peculiar SSIS addition

ActiveX Script Task
Another task that I suspect has never seen the light of day in a SSIS package. It was billed as a way of running upgraded DTS2000 ActiveX scripts in SSIS – sounds good except for one thing. Anytime one of those scripts would try to talk to the DTS object model (which they all do – otherwise what’s the point) then they will error out. This one has always been a real head scratcher.

Slowly Changing Dimension wizard
I suspect I may get some push back on this one but I’m mentioning it anyway. Some people like the SCD wizard; I am not one of those people! Everything that the SCD component does can easily be reproduced using other components and from a performance point of view its much more beneficial to use those alternatives.

Multifile Connection Manager
Imagining buying a house that came with a set of keys that didn’t open any of the doors. Sounds ridiculous right? How about a SSIS Connection Manager that doesn’t get used by any of the tasks or components. Ah, that’ll be the Multifile Connection Manager then!

Comments are of course welcome. Diatribes are assumed :)


Published Tuesday, May 11, 2010 6:22 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Todd McDermid said:

I'm on board with all those, Jamie - I'll see if I can think up five more to throw under the bus...

May 11, 2010 12:37 PM

Bert said:

Web Service Task has never been used in anything but trivial samples.  It should die. Horribly. Painfully.

May 11, 2010 1:34 PM

Todd McDermid said:

My five:

1. The Properties Window

2. The Data Profiling Task

3. The Shrink Database Task

4. Lineage IDs

5. The "Advanced" Editor

May 11, 2010 5:53 PM

Boyan Penev said:

Absolutely right - we have way too many redundant tasks in SSIS. As per your previous post about inconsistencies around the different tools (SSIS, SSAS and SSRS) it would be way better if we get some improvements to the currently existing tools rather than getting a lot of useless ones.

Although, recently I had a number of SCDs and most of those were really small dimensions (2-100 rows per dim table). The SCD task can be used for them without taking a significant performance hit. However, it would be good to re-do it so it is simpler to use..I find it hard to modify it and the wizard does assume a thing or two...

May 11, 2010 6:05 PM

Mike C said:

I agree the SCD wizard sucks, but it should be completely rewritten from scratch.  Most SSIS folks I know run straight to a high-performance component like TableDifference to manage SCDs or use a staging table strategy with MERGE to avoid the pain of the SCD wizard.

May 11, 2010 7:38 PM

lewieo said:


In 2007 you created a video on YouTube about how to consume a webservice through 2008.  Can you provide an example or post the package you wrote.  The video is very grainy.  I can't pick out some of your tips because I can't see it.

May 12, 2010 12:07 PM

mark blakey said:

Ive only seen one reasonable use for datasources and that is for one off data migrations where the packages share the same datasource.

Still easy to achieve the same thing with config files I guess...

May 13, 2010 5:47 AM

Brad C said:

Data Source Connections - I would probably keep this feature.  Idea of eliminating abstraction doesn't feel right.  If connections were managed at project and never renamed in the package, all packages across the project would have consistent names and the single project config file would not bloat from the extra names for connections to the same source.

May 15, 2010 2:23 PM

daniel said:

SCD outta there yeah sure. But you need a good alternative. One such is the Kimball SCD Component on Codeplex.

May 16, 2010 10:38 PM

Ryan said:

Not sure why everyone is bagging on the web service task.  We've got several SSIS packages in our environment that are utilizing both our own custom web services, custom services delivered from vendors, and canned services delivered via integration layers on apps such as SharePoint.

Sure, you have to handle arrays and such in a script task but never had a problem with the web service task itself.

May 17, 2010 10:02 AM

Jon said:

This might be in another post but the one item that SSIS really needs to add is a regular expression component. I have been work with data for years in Unix and using regular expressions is like mother's milk. The lack of that component forces me to resort to writing VB scripts in the script component. A simple data matching on regular expression would be most helpful.

There are third party add-ons on codeplex, but it would be nice to have out of the package.


May 18, 2010 4:50 PM

Sam Loud said:

I think you're being a bit harsh on the SCD. I freely admit it's not the most performant of components, but for a quick and easy way of configuring a small upsert it's a useful tool to have at your disposal.

Would SSIS really be improved by taking it out, and forcing you to create tables and write MERGE statements, or by downloading and installing TableDifference absolutely everywhere? I don't think so.

May 19, 2010 7:54 AM

LEverest said:

@Sam: I haven't used the SCD in over two years in production; I'm going other routes and have been fairly happy with the outcomes. You nailed it, though - lots of folks read about performance concerns and opt out.


May 19, 2010 8:57 AM

jamiet said:

I'd just like to point out that if the SCD wizard weren't in there it doesn't mean you'd have to resort " to create tables and write MERGE statements, or by downloading and installing TableDifference "

You can achieve the same as the SCD wizard using out of the box components and it'll be a lot quicker.

May 25, 2010 8:36 PM

Lee said:

"You can achieve the same as the SCD wizard using out of the box components and it'll be a lot quicker."

Can anybody give me a clue at how you do this, and process type 1 and type 2's? Anybody willing to forward a sample package as I hate using the SCD wizard and its bad performance.

July 14, 2010 10:09 AM

Leave a Comment


This Blog


Privacy Statement