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:

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

Comments

• Anonymous
  March 13, 2008
  Thanks for that Andrew - that advice will certainly ease our migration. There had been some worries about the amount of DTS that needed re-written.

• Anonymous
  March 19, 2008
  Hi Andrew, thanks for this great post. I agree with most your items but I'm afraid I must disagree with your last sentence though about keeping DTS when you have less than 20 packages. From our customer experience, customers that do this are extremely dissatisified. A few reasons: lack of true 64 bit support for the DTS runtime (dtsrun.exe), lack of a method to easily create new packages and lack of logging. The lack of a method to create new DTS packages can be worked around by modifying an existing package and doing a Save As but it is very awkward. On the logging side, in each package, you can create a Package Log as you did before but the UI to see those logs is gone. You would have to create your own reporting to view Package Logs in DTS under SQL Server 2005. This creates a huge manageability issue for many customers. I wrote up a quick performance study about SSIS vs DTS here as well and it is easily 65% better in performance without doing at all other than upgrading. This to me shows that Microsoft got tons right in the SSIS engine but DTS support isn't one of them. http://pragmaticworks.com/community/blogs/brianknight/archive/2008/03/12/ssis-lab-runtime-performance-differences-between-dts-and-ssis.aspx

• Anonymous
  March 25, 2008
  Brian I Totally agree - I wasn't suggesting you keep your packages in DTS if you have less than 20 of them, just that it may be cheaper for them to convert them manually rather than invest in DTS-XChange.

• Anonymous
  March 28, 2008
  Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence