How to migrate DTS packages to SSIS 2012?

ssis dts
user1806751 picture user1806751 · Nov 7, 2012 · Viewed 16.7k times · Source

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?

Answer

Pete Carter picture Pete Carter · Nov 7, 2012

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.

Related questions

How to create an SQL table and and populate it with Excel spreadsheet data?

Is there an easy way to create a table in SQL Server (2005) from an Excel spreadsheet. I'm thinking maybe some tool? Thanks in advance.

Read More
DTS_E_OLEDBERROR. Error code: 0x80004005.Difference between SQl Native client and oledb provider for sql server

There was a communication link error while I was using SQL Server Native Client 10 in an SSIS Data Flow component. Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record …

Read More
SSIS importing datetime column into SQL Server 2008

I am trying to import a PSV file into SQL Server 2008 using SSIS. All is working fine apart from one field that contains a datatime. The contents of the file being imported contains datetime in the format of 2012-08-08…

Read More