MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, May 14, 2009

SSIS- DTS to SSIS Migration

When you upgrade an existing SQL Server instance, DTS runtime remains in place. Your DTS packages are not affected and local server packages remain stored in the msdb.dbo.sysdtspackages table. Structured storage files, or packages saved to DTS files, are also unchanged.If you use Meta Data Services, previously called the Repository in SQL Server 7.0, you may encounter some problems. Meta Data Services is not supported by SSIS, which is not surprising considering that SQL Server 2000 Service Pack 3 removed Meta Data Services as a storage location. This is not a significant change, however, since the existing DTS object model and the DTSRUN command-line tool continue to support Meta Data Services.The most important thing to remember about the DTS-to-SSIS upgrade is that after it is complete, your packages will continue to run. Scheduled jobs will still use DTSRUN and call the same packages from the same locations as they did prior to the upgrade.When making the upgrade, you will need to determine how you will manage these legacy DTS packages in the future. The DTS runtime will continue to be available, and will have been updated to enable DTS packages to connect SQL Server 2005 data sources. But the DTS designer components are removed along with SQL Server 2000 Enterprise Manager.
See for more info:

http://bisqlserver.blogspot.com/2006/11/migrating-your-sql-2000-dts-packages.html
http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/

No comments: