Sunday, December 10, 2017

SQL Server - DTS to SSIS Migration

1.     Need of DTS to SSIS Migration


DTS was first released with SQL Server 7.0 with a objective of helping the user in  import, export, and/or transform data from various sources to single or multiple sources. Now Data Transformation Services (DTS) has been replaced by SQL Server Integration Services and SQL Server 2008 Setup does not install DTS support. Data Transformation Services (DTS) is deprecated. We should plan to remove dependencies on DTS in anticipation of future releases of SQL Server. so there is a need to upgrage or migrate the DTS to SSIS packages.

2.     Ways to Upgrade DTS to SSIS 2008 R2

      In Place upgrade
      Side by Side installation
      New installation

3.     DTS to SSIS Migration Plan

      Run the Upgrade Advisor for DTS
      Fix the issues suggested by Upgrade Advisor
      Choose the Migration method
      Migration Wizard to convert DTS packages to SSIS
      Rewrite the DTS packages to fully leverage SSIS capabilities
      Use a third-party tool (such as DTS xChange from Pragmatic Works Software)
      Continue to run and modify existing DTS packages. This is not applicable if migrate to SQL 2012 in future.


                                  Fig. showing DTS to SSIS Migration / Upgrade Process 

4.     DTS Package Migration Wizard

      Migration wizard converts DTS to SSIS in large extends
      Some portion need to re-write after migration
      Migration wizard can be run from either SQL Server management Studio or Business Intelligence studio

5.     Without Migration / Upgrade

      We can directly import the DTS to SQL Server 2008 and can be utilized.
      This approach will not work when need to migrate DTS to SQL Server 2012 because SQL Server 2012 has deprecated all the features of DTS.

6.     Completely Re-Write

      Upgrade advisor is not required
      Efforts required are more as compare to other approach.
      It is the best approach to avail all the benefits of new technology
      Upgrade advisor is not required
      Efforts required are more as compare to other approach.
      It is the best approach to avail all the benefits of new technology