DTS 2000 Migration – Start planning for 2011…

Microsoft has told us that DTS has been deprecated. I talked about this in a previous blog entry. It means we have been warned that it will no longer be supported in a future release. When SQL Server 2008 comes out shortly, Microsoft will only support two previous releases namely SQL Server 2000 and 2005. So any DTS packages running under SQL 2000 will continue to be supported until the next release, presumably slated for 2011, if Microsoft sticks to its stated policy of three-year intervals. In other words, we have until 2011 to migrate our DTS 2000 packages to SSIS.

The migration of DTS packages caused a bit of a stir during the Yukon Beta back in 2005 when Microsoft let us know that 100% of our DTS code may not be translated into the "new DTS". You see, DTS was rewritten from the ground up with a new architecture to provide more scalability and eventually was renamed to SQL Server Integration Services (SSIS). This was a good idea since it really is a very different animal from DTS but the change was so late in the Beta program that developers had no time to rename the executables so you will see some DTS remnants "under the hood". For instance, we can execute an SSIS package using DTEXEC. Understandably, users who had spent 5 years or more maintaining their complex DTS packages were a little aggrieved and let Microsoft know that they would not upgrade if it couldn't guarantee a smooth migration.

So the Microsoft developers did a clever thing. They decided to allow the use of DTS within SQL Server 2005. Within SSMS there is a Legacy folder (under Management) that allows you to import an existing DTS package. And with a free download, you can install the good old DTS Designer and maintain and test your DTS packages within SSMS. This is not emulation software, it is the real thing so anything that ran in DTS in 2000 will run in 2005. And now 2008. Complete backward compatibility. This means that we can decide if and when to migrate a particular package to SSIS on a case-by-case basis. We can even have a new SSIS package execute an existing DTS 2000 package if we wish. Co-existence - a good strategy. When we want to benefit from the new scalability of SSIS, we should use the SSIS Migration Wizard to migrate the package, but we have to remember to schedule time for code changes and re-testing of the package. Now we have a full Debugging facility within Visual Studio which makes things easier. In many cases, it may be necessary to rewrite the package from scratch based on the new features of SSIS. The good news is that the Migration Wizard leaves the existing DTS package untouched while you work on the new SSIS package. The new extension is .DTSX (X for XML) as opposed to the old .DTS - another remnant.

The DTS backward compatibility solution calmed everyone down and SQL Server 2005 was released as planned. So co-existence is a fine strategy - until 2011 that is.

Good luck!

Brian

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Related:

Copyright © 2008 IDG Communications, Inc.