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?