I have to say it, I love the new SQL Server 2005 Integration Services (SSIS). Wow. There is some really awesome stuff in there that really just blow me away. There are so many new things in SSIS that I just love - too many to mention. However, it is some of the smaller, less noticed, features that have come to be my favorites. Here are my top 3 small features in SSIS (so far).

The new DTSX extension is now associated to SSIS. When you save a SSIS package as a file (which I find I do quite often now with SQL2005 instead of saving them in the msdb). What this association does is two things; 1) You can double click on a DTSX file and get a dialog to edit the package properties, such as connections, commands, configurations, etc (see dialog below). You can also execute the package from this dialog. Just awesome.

(Click for larger image)

All you need to do is double-click a DTSX file to get this dialog (or right-click and select 'Open'). No need to run dtsrunui and then browse for it like you would have to with DTS files (but that wouldn't give you the same ability to edit properties like you can here, only global vars), although you can still do that if you want to for saved DTSX files (or any SSIS package) by running DTExecUI. If you right-click on a DTSX file you can select 'Edit' which will open the package in Visual Studio's BI designer for editing.

You have complete control over flow. The part where this really hit me is the granularity of control you have over flow for individual column transforms.

You can specify to redirect any rows where a certain column's lengh might be truncated or where there might have been an error for the data in a critical column. The redirect thing just blows my mind. How did we ever survive without that in old-school DTS? (I'll tell you how, with a lot of pain in the butt flow control and failing tasks just because some non-important value would have been truncated. That just sucked). Control is good. More detail the better.

The Package Explorer rules. Not much more you can say there.

You have a complete bird's-eye view of the entire package. But not just for viewing, but also for a quick and easy way to modify properties in the package. No need to go around clicking on tasks or other items in the designer, you just have the hierarchy of items in the package and can easily select one and modify it's properties in an easy to use standard property list. Way too cool.

It's those small things that make me so happy *snif*.