How to migrate DTS packages to SSIS 2012?

Question

I need to migrate all the DTS packages created in SQL Server 2000 to SSIS 2012. What are the differences between SQL Server 2000 and SQL Server 2012. Are there any differences in SQL statements like Insert, Update, Delete etc. What are the things I should be aware of to upgrade the DTS packages to SSIS?

Solution

You cannot migrate package directly from DTS written in SQL Server 2000 to SSIS in SQL Server 2012. You could do an intermediate conversion from DTS to SSIS 2005 or 2008 and then upgrade them to SSIS 2012 packages, but I would not advise this, as the conversion wizard is not brilliant and you will also loose most of the benefits of SSIS over DTS.

Therefore, I would strongly advise re-writing the packages in SSIS 2012, replicating the functionality of your original DTS packages. I appreciate that depending on the number of packages involved, this may be a big, time consuming task, but it is the best way.

In terms of differences, I have listed a few more notable ones below:

• DTS was COM based, and although under the covers SSIS still uses many COM objects, it is wrapped in .NET
• SSIS has sequence containers so that objects can be grouped together
• SSIS 2008 and 2012 support C# as well as VB.NET
• DTS only allowed mapping column names, but SSIS has a rich set of data transformations
• ActiveX scripts, if any, in your DTS package have to be thrown away
• In SSIS you need to map Unicode and ASCII manually
• SSIS supports 64-Bit

There is no difference between SQL Server 2000 and SQL Server 2012 in terms of basic DML, such as INSERT, UPDATE and DELETE, but SQL 2008 onwards also has a MERGE statement which allows UPSERTs. This is not supported properly in SSIS natively, but there are third party UPSERT components, including a free one on CodePlex.com

Amongst the SSIS Data Flow transformations, however, there are components such as a Slowly Changing Dimension (SCD) component and a OLEDB Command component which allows you to update rows. however, both of these transformations poorly performing and there is usually a better way.