DTS to SSIS Migration Project

With much fanfare, SQL Server 2012 launched earlier this year. With all the excitement about the new features, such as AlwaysOn Availability Groups, lots of new T-SQL functions, and ColumnStore Index, there are also features that go away. With each release of SQL Server, features are deprecated, and, eventually, those features are removed from the product.

Two things that will impact many shops are the end of SQL Server 2000 compatibility level and the end of DTS. I have spoken with lots of DBAs still using DTS about the reasons to take the plunge into SSIS, and have also helped many of them get started with SSIS.

So, what do you do if you have tens, hundreds, or even thousands of DTS packages still running? You are going to have to do some work! Luckily, there is a product from Pragmatic Works called DTS xChange that can save you a ton of time.

OK, sure, I do work for Pragmatic Works, but after just coming off a DTS to SSIS migration project, I can tell you that DTS xChange works great. After navigating through the wizard, it took about 1 minute per package for DTS xChange to do the work on my customer’s DTS packages.

Of course, there is still some cleanup to do once you have your new SSIS packages. The tool does a great job, but it can’t clean up ActiveX Scripts, for example.  The ActiveX Scripts are left as ActiveX scripts and might work, but you definitely want to eliminate them from your SSIS packages.

At my customer, I found that most of the ActiveX Scripts created, archived, or deleted log files. SSIS has many built in tasks, such as theFile System Task and ForEach Loop Container, that can easily replace those scripts.

One other interesting thing is the old Dynamic Properties Task in DTS. There is really not an equivalent in SSIS 2005 through 2008R2. When DTSx Change encounters one of these, it creates a Script Task (not the old ActiveX Script) to set the properties. The Script Task it creates is pretty cool. It has functions for reading from all the sources a property could be set in the Dynamic Properties Task: INI file, query, global variable, environment variable, constant or a file.

When reviewing my customer’s packages, I found that I could just delete the new scripts and use the functionality provided by the Configuration Files. Don’t assume this is the case with your packages; be sure to review them thoroughly.

Overall, I enjoyed working with DTS xChange and definitely recommend it if you happen to have many or even a few DTS packages to convert.