Archive for November 2008

SQL Heroes Contest Winners

They announced the SQL Heroes contest winners at the PASS Summit keynote today. I’m very happy to say that both BIDSHelper and ssisUnit were in the top 5! BIDSHelper had a perfect score of 55 (the only perfect score), based on the judging criteria (yes, 55 was the max you could score). ssisUnit tied with Extended Event Manager, from Jonathan Kehayias, with a score of 45.

Thanks for voting, and please keep the suggestions for improvements coming in.

Batch Destination and the Merge Destination

After I created the Batch Destination for my presentation at the MS BI Conference, I was talking with Matt Masson, who let me know that they’d been working on a MERGE Destination sample component, and would hopefully be releasing it soon. It’s out now on the CodePlex site for the Integration Services team samples. I’ve taken a look at it, and it is very nicely done.

What’s the difference between the two? The MERGE Destination has a much nicer interface, that lets you graphically build a MERGE statement. And, of course it uses the MERGE statement to perform updates and inserts in a single operation. This is nice, but limits you to using a SQL Server 2008 database as a destination. The Batch Destination executes any SQL command you give it, so it can be used on SQL Server 2005 or 2008.

I haven’t done a performance comparison between the two, so I’m not positive which one is faster. However, the MERGE Destination stores the working data in memory, so it should be more efficient at loading the data. This does require the MERGE Destination to create a type and a stored procedure in the destination database, so the appropriate permissions need to be provided. The Batch Destination uses the ADO.NET Bulk Copy functionality, so the data is persisted in a working or temporary table in the target database, and cleaned up afterward, which is likely to be slower. However, you don’t need any special permissions to create a temporary table.

This isn’t intended to be a “this one is better than that one”. I can see using either or both as circumstances dictate. I just think it’s nice to have options.

SSIS Tasks and Components

Todd McDermid (a frequent poster on the MSDN SSIS forums, and creator of the Kimball SCD component) and I have started a new project on CodePlex along with Todd McDermid, the SSIS Community Tasks and Components (ssisCTC) project. This project is intended to be a umbrella project for custom SSIS tasks and components that are released as open source, as well as provide a listing of other open components for SSIS. I’ve published the Batch Destination component that I created to the site, and intend to add some more over the coming months.

Why set up a community project around this? Primarily, because I’ve found that many single person projects get one release, and then are never touched again. Having multiple people involved helps insure that if one person gets busy, or moves on to other interests, there are still people around to maintain and enhance what’s been put out there. So, if you are interested in joining or contributing, please let me know, or get in touch with use through the CodePlex site. Also, if you have or know of a freely available SSIS component or task, let us know, and we’ll be happy to add it to the list.