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.
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
In Place upgrade
•
Side by Side installation
Side by Side installation
•
New installation
New installation
3.
DTS to SSIS
Migration Plan
• Run
the Upgrade Advisor for DTS
the Upgrade Advisor for DTS
• Fix
the issues suggested by Upgrade Advisor
the issues suggested by Upgrade Advisor
• Choose
the Migration method
the Migration method
• Migration
Wizard to convert DTS packages to SSIS
Wizard to convert DTS packages to SSIS
• Rewrite
the DTS packages to fully leverage SSIS capabilities
the DTS packages to fully leverage SSIS capabilities
• Use
a third-party tool (such as DTS xChange from Pragmatic Works Software)
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.
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
wizard converts DTS to SSIS in large extends
• Some
portion need to re-write after migration
portion need to re-write after migration
• Migration
wizard can be run from either SQL Server management Studio or Business
Intelligence studio
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.
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.
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
advisor is not required
• Efforts
required are more as compare to other approach.
required are more as compare to other approach.
• It
is the best approach to avail all the benefits of new technology
is the best approach to avail all the benefits of new technology
• Upgrade
advisor is not required
advisor is not required
• Efforts
required are more as compare to other approach.
required are more as compare to other approach.
•
It is the best approach to avail all
the benefits of new technology
It is the best approach to avail all
the benefits of new technology