SQL Server 2012 SSIS upgrade considerations

03 August,2013 by Tom Collins

Question: We’re planning an upgrade from SQL Server 2005 to SQL Server 2012. We have over 100 DTS packages? Can I migrate the DTS packages or do I need to upgrade to SSIS?

Is there a smooth path to upgrade to SSIS?

Answer: DTS is  discontinued in SQL Server 2012. Depending on the complexity of the existing DTS packages , plan for a considerable effort to migrate the DTS packages to SSIS

In my recent SQL Server upgrade projects, a key resource consideration is to estimate the amount of work required to transform DTS to SSIS packages. On of my tactics is to establish a system for the upgrade - giving DBAs, developers and project managers an outline of how to progress

The First step is to transform all DTS packages into SSIS packages in the current SQL Server environment. If the existing environment is SQL Server 2000 , set up a development server using SQL Server 2012

The Second step is to use the Upgrade Advisor , reporting on issues requiring attention. There is a restriction on using SQL Server 2012 Upgrade Advisor to analyse SQL Server 2005 SSIS packages.

Upgrade Advisor requires SSIS 2005 installed and running on the same machine in order to scan SSIS 2005 package.

32 bit v 64 bit – With every version upgrade this becomes less of an issue. But there is one well documented gotcha. If  MS Excel is used as a source or destination in dataflows.SSIS in 32 bit mode is required. There’s a great post on this issue on: http://hrvoje.piasevoli.com/2010/09/01/importing-data-from-64-bit-excel-in-ssis/

Read More

SSIS version in SQL Server

SQL SSIS and service pack upgrade

How to troubleshoot a slow running query in SQL Server

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server 2012 SSIS upgrade considerations

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer