OdeToCode IC Logo

DTS in SQL 2005 - Good News and Bad News

Thursday, April 12, 2007

DTS has always worried me. Unfortunately, I have 331 reasons to worry:

PS > gci -r -i *.dts | group Extension
Count   Name
----- ----         . . .
  331   .dts 

Five years ago, ADO.NET didn't have a bulkload API, and DTS seemed like the best tool for moving and transforming millions of records. This was despite the fact that the binary DTS packages check into source control as opaque blobs, and the cumbersome UI makes even simple changes difficult. We thought we'd need to write and distribute ~50 packages total, but feature growth and support for additional 3rd party databases has ballooned the original estimate.

The application using these packages has to support SQL 2000 for the foreseeable future. Not every customer has budgeted for a new SQL 2005 license, unfortunately. The good news is that SQL 2005 will run the DTS packages, even though it cannot migrate but a handful of them to the new SSIS platform.

Another piece of good news is that you can edit DTS packages in the SQL 2005 Management Studio after downloading some designer components. The bad news is that the designer is still suboptimal. For instance, the Home, End, Backspace, PageUp, and PageDown keys will quit working in the designer. The good news is that this problem is fixed in SP2. The bad news is that the fix changed the designer into a modal dialog. Modal dialogs should be banned from all software in the universe.

If you haven't started your DTS migration yet, here are a few resources I've collected.

DTS Services for SQL 2005 FAQ

SQL Server 2005 Integration Services Backward Compatibility

Known Package Migration Issues

A tool to improve DTS to SSIS migration

Any other good ones out there?