SQL Server
During the Beta program of Yukon way back in 2005, Microsoft let us know that the new DTS (soon to be renamed to SSIS) would not be able to translate 100% of the DTS 2000 scripting code we had been working on for the previous 5 years (or longer). Well, there are still many customers working on that migration. Maybe it’s easier to go straight to 2008? A recent project I was working on was to do just that…convert DTS 2000 packages to SSIS 2008. The big problem area is ActiveX scripting tasks. SSIS Migration wizard does not do too much with these tasks. In fact, it just copies the code over that is doomed to fail using the SSIS Object Model. The recommendation is to completely rewrite these tasks so you can use VB.NET, C#.NET, Intellisense, Debug and all that good Visual Studio stuff. That’s what we should do. But that takes time. Maybe a long time. Of course, the Microsoft compromise was to allow DTS packages to be run natively in SQL Server 2005 and now 2008. We can still use DTSRUN as long as we install the DTS Runtime. This is part of the Backward Compatibility Feature Pack: http://msdn.microsoft.com/en-us/library/ms143755.aspx Also, you can run a DTS 2000 package, as is, from an SSIS Package using the aptly named Execute DTS 2000 package task. This effectively submits the DTSRUN statement for you from within an SSIS package. If you imported the DTS package to SQL Server 2005 or 2008, you can tell your boss that your packages are now running in SSIS, even though you have not had to make any code changes. If you have migrated your SQL 2000 databases, you can turn off that SQL 2000 box and retire it. Everything is now off the legacy server. But what if you want to make a small change to your DTS package without having to migrate completely to SSIS Scripting? Well, you can install the DTS Design Components in SQL 2005 or 2008. Then you can use SSMS Legacy folder and launch the DTS Designer from there, or from within BIDS. This again is a download from the same technote: http://msdn.microsoft.com/en-us/library/ms143755.aspx However, there is a bug in the install (which actually is the same install for both SQL 2005 and 2008). You need to play with the PATH environment variable to make sure that the “80ToolsBinn” folder is before the “100ToolsBinn” folder. The install MSI puts it at the end, so you get the same error again. The technote (now officially in Books Online) tries another tack: copying DDLs and RLLs from various folders (take a look!). On top of that, the instructions are not strictly accurate so make sure you scroll down to the bottom of the online version to see the bug report for the bug correction! I am sorry, since it is now documented, it is no longer a bug; it works as designed and is now a feature. It’s lucky that, as DBA’s, we have to have a sense of humor, isn’t it? That is, until 2011 when DTS is deprecated… cheers Brian
