How to migrate DTS package from Server A to Server B

Generally, we can migrate the DTS package from Server A to Server B as below:

Method 1
1. Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.
2. Save the package on the destination server to the SQL Server.
 
Note You have to move each package one by one in separate files.

Method 2
1. Open each DTS package in the DTS Designer.
2. On the Package menu, click Save As .
3. Specify the destination SQL Server.

Note The package may not run correctly on the new server. You may have to change the package, and change any references in the package to connections, files, data sources, profiles and other information located on the old source server, to reference the new destination server. You must make these changes on a package by package basis based on the design of each package

However, if number of DTS packages is large, for SQL Server, there is no native facility to migrate such large number of DTS packages easily. The best utility I have found is from a 3rd party.  Here is the pointer to the utility.
http://www.databasejournal.com/features/mssql/article.php/1462601

Also, if we may have to change the package, and change any references in the package to connections, files, data sources, profiles and other information located on the old source server, we can implement by using sp_OA* stored procedures. For detailed script, please refer to:
Moving DTS Packages
http://www.dbazine.com/sql/sql-articles/larsen8
 
For the scripts mentioned in the above article:
---Before using the sample code to migrate the DTS package, please:
a. Backup the msdb database from the SERVER1 & SERVER2
b. Using Domain account to login current windows and make sure this account are in the sysadmin group of SQL instance SERVERA & SERVERB
---Here is the limitation of this script:
a. Encrypted package is not supported.

Thanks and regards,
Michael

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值