Upgrading Data Transformation Services (DTS)

One of the biggest headaches in migrating away from SQL Server 2000 is Data Transformation Services (DTS).  The tool is very widely used and its initial simplicity no doubt contributed to that. A good example is of several Oracle sites where SQL Server was sat in a corner simply to move data between Oracle databases using DTS.

But once you wanted to do anything sophisticated in DTS then its initial appeal rapidly waned, for example loops and proper error handling where often impossible to implement together.

So in SQL Server 2005 the whole thing was replaced by Integration Services (SSIS) and although there was a migration tool the resultant packages were hard to understand and reminiscent of using the wizard to design packages in DTS.

So my advice is to keep things running in native DTS mode, which will also be available in SQL Server 2008, until such time as you need to re-engineer a package because it needs to change anyway, perhaps because of a schema change in the source or target.  This approach will mean that in three years time when the next version of SQL Server is released you won't need to worry that DTS is no longer supported in that version, because you will have done all the migration already.

I have noticed another approach and that is to use a third party tool, DTS xChange, which is very comprehensive solution to the problem, and as far as I'm aware is the only tool in this space.  I was impressed that it allows for packages to be converted but also for them to be  logged and to be transaction aware as required.  There's a good on-line demo on the site:

image

So if you have more than about 20 migrations to do this tool would probably be worth investing as it will save you more time than it costs.

Technorati Tags: SQL Server 2008,DTS,Integration Services,SSIS,DTS xChange