RSS 2.0 Feed
RSS 2.0

Atom 1.0 Feed
Atom 1.0

  Favorite New Features of SSIS 

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*.


Leave a comment below.


  1. Gravatar
    I compare the data integration offerings of Microsft, with the release of SQL Server 2005, and IBM with the Ascential products. With major releases from both companies 2006 is shaping up as a data integration slugfest.
  2. Donald Farmer 2/28/2006 8:40 AM
    This is a great post, Ryan. It's good to see someone paying attention to the small details! It's very satisfying to see some little feature we have spent time on making a difference to the extent that it becomes a "must have" for a user.

    I hope you're continuing to enjoy working with SSIS and that you'll post more - warts and all - in the future.

    Donald Farmer
    Group Program Manager
    SQL Server Integration Services
    Microsoft Corp.

    Integrate - Analyze - Report
  3. Amy Haiken 4/21/2006 9:17 AM
    Hello Ryan!

    I'm using the SSIS Package Migration Wizard to migrate SQL Server 2000 DTS packages. I followed all of the steps documented in Microsoft's 'SQL Server 2005 Books Online Using the Package Migration Wizard'. After I completed the migration, it says "you should connect to the local instance of Integration Services in Object Explorere to see the migrated packages. If you selected SQL Server as the destination, the migrated packages will be visible under the MSDB node'." When I checked, nothing showed up in this area. The logs showed that the migration completed successfully, and the dbo.sysdtspackages90 showed all 22 packages that I migrated. Additionally, if I tried to use the wizard a second time, it tells me that the files have already been migrated.

    Have you experienced this problem? If so, how did you rectify the problem? If you successfully migrated the SQL Server 2000 DTS to 2005 with the migration tool, could you tell me how you did it and where you were able to find the migrated packages in SQL Server 2005?

    Amy Haiken
    Software Engineer
  4. sundar 7/17/2006 11:51 PM
    This is not related to topic but dont know whereelse to put it.I am redirecting rows to an error table in database. Now my complete redirected row data comes in single column 'FlatfileSource Error Output column'. In this column, I need to extract the value of a column named 'CompanyNumber'.Ican extract it using split function in script but problem is I am getting data from 5 flat files and position of this column is different in different flat files.So when I union all the redirected rows from all 5 flat files, I never know which column to extract. So how do i know in data flow, the company numbers which are redirected.Thanks for ur patience to read this long stuff...
    will be great if anyone can answer
  5. reticon 1/8/2007 9:47 AM
    Some warts:

    "Only WINDOWS AUTHENTICATION is available for SSIS." - Great, so you are connected to the database as SA but since your NT user account is just a peon you cannot do diddly. Brilliant! If you support mixed mode on SQL Server you have a responsibility to support it on the related services. This problem is readily visible and obvious because the native sql login info is still tauntinglly visible, but terminally greyed out.

    "Do not use SERVERNAME\INSTANCENAME because SSIS does not support multiple instances..." - KRIKEY!

    For anyone else who finds this ridiculously non-intuitive... in order to access your DTS packages ... ooops (SSIS Packages) you must connect seperately to Integration Services ... and beware of the above steps backward in your new investment. Also note, as far as I can tell you can no longer edit the package from within the manager.

    GEESH! Guys, didn't documenting these inadequacies just feel a bit cruel?

    Anyway, on the bright side SQL Server Agent is still available where you expect it. For me the sad reality is someone has basically wasted millions of future developer hours by including such a steep curve for loyal customers who were once SQL Server experts. Now, I'm just a fish out of water. Ever since installing SP1 I can't even modify a table column in the server mangler. I just get an empty gray window.

    Migrating even simple legacy DTS packages will result in errors like:

    ... oops, I can't copy from that window. There are dozens, some bologna about unicode and blaaa blaaa... This package has worked for 3 years, but thanks to SQL 2005 it's back to the drawing board. Like I said, millions of hours.

    If anyone knows, is there a way to execute a legacy package from SQL Agent in SQL 2005? I only spent a few of those millions of hours trying to find out how, but no joy. :-\ ... and no, I don't want to make a SQL2005 SSIS package to do it.

    That is another bright side btw... if you download the right dohicky you can import and run old 2000 packages. The one above with the dozens of validation errors, well it works fine in that window. Just good luck trying to schedule one. :-0
  6. KevCal 1/24/2007 3:45 PM
    You can run SERVERNAME\INSTANCENAME, you just have modify the config file for SSIS. Check out the below info in BOL:


  7. KevCal 1/24/2007 3:46 PM
Comments have been closed on this topic.



Also see my CRM Developer blog