Community Tasks and Components for SSIS

About a month ago at the Microsoft BI Conference, I managed to intoduce myself to John Welch - a very nice guy with a long pedigree working with SQL Server Integration Services.  (Read his BI Thoughts and Theories blog.)  I'd signed up for his Chalk Talk session about creating custom components and even went so far as sending him an email asking him to take a look at my own custom SSIS component.  He continued to talk to me after that - that's why I call him a really nice guy.

During that session, John showed a custom component he'd made to help with the unavoidable problems around pushing UPDATEs to a SQL table.  If you've used SSIS, you know that in those circumstances where you need to update existing records in a table, your only simple tool happens to be the Ole DB Command transform.  The problems you run in to with the Ole DB Command are (unfortunately) plentiful:

• It is slow.  The transform operates (as it should) on a row-by-row basis, issuing your manually typed-in command once per row to the database.  The net effect: one "round trip" per row.  Even if your database is running on a Cray, if it's 10ms away down the network, processing 100,000 rows will take over 16 minutes.
• It is cryptic to set up parameters.  The whole deal of mapping columns to "param0" is just, IMHO, insanely bad.

Sure, there are other ways to get an UPDATE done, but they either require a significant array of transformations operating together, special database rights, and/or Scripting code.

Good news for you out there that are less than enamored with the Ole DB Transform - you now have two alternatives.  One is the Batch Destination that John demoed and autopsied at the conference, and another is the Merge Destination that Matt Masson and the MS SSIS team constructed.  They're both appropriate for different scenarios and constraints.  Read more about those on John's blog.

During some discussions with John, I lamented the fact that there didn't seem to be a central place where a person could go to find out what solutions the community had generated to fill out the capabilities of Integration Services.  I happened to be shocked that nobody had created (what I'd consider) a "real" SCD component - but then again, even after searching the internet, I couldn't really be sure - because you never really know what keywords these things might get tagged with.  What I wanted was some kind of a directory (Apple fans might call it an AppStore) that contained searchable listings of components.  John had the idea of creating a project on CodePlex to do that - since a great deal of custom components were hosted there anyway.  The only drawback to that idea was that you can't create a project on CodePlex without source code, but John's now fixed that - he's uploaded his Batch Destination component there.

So if you ever run into a situation in SSIS that seems "too complicated", search the >SSIS Community Tasks and Components project on CodePlex - there may be a solution there for you.  If you find an open source component that we don't happen to have listed, please post it and help your community.  And most of all, if you have a situation that you've "solved" through a complicated Script, or through use of multiple other components - and you use that "solution" frequently, add an Issue to the project with a description, and some enterprising individual may undertake to package it up in a custom component...