Using the Package Migration Wizard
SQL Server 2005 Integration Services (SSIS) provides the Package Migration Wizard for migrating Data Transformation Services (DTS) packages that were created by using the SQL Server 2000 tools.
Note
The Package Migration Wizard is available in the Standard, Enterprise, and Developer Editions of SQL Server 2005.
Note
Upgrade Advisor cannot analyze, and the Package Migration Wizard cannot migrate, DTS packages that are saved in the msdb database of an instance of SQL Server 7.0. However the Upgrade Advisor can analyze, and the Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved as structured storage files, or that are saved in the msdb database of an instance of SQL Server 7.0 that has been upgraded to SQL Server 2005.
Not all packages can be migrated completely. For example, some SQL Server 2000 tasks do not map to Integration Services tasks, custom tasks cannot be migrated, and some scripts in ActiveX Script tasks or in data transformations cannot be migrated. For more information, see Migrating Tasks and Known Package Migration Issues.
Packages left in the SQL Server 2000 package format can be used in Integration Services solutions and can also continue to run as stand-alone SQL Server 2000 packages.
Many SQL Server 2000 packages are password-protected. If you migrate these packages, the Package Migration Wizard prompts you for their passwords. You cannot migrate a password-protected package unless you provide the correct password.
SQL Server 2005 does not install support for Meta Data Services. The Package Migration Wizard supports Meta Data Services (Repository) packages only if SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files are installed on the local computer. When the Repository files are present, the Package Migration Wizard can migrate DTS packages that were saved to Meta Data Services. When the Repository files are not present, the Package Migration Wizard can only migrate DTS packages that were saved to SQL Server or to structured storage files.
Start the Package Migration Wizard
You can start the Package Migration Wizard from three locations.
- From SQL Server Management Studio. Connect to an instance of the SQL Server 2005 Database Engine, right-click the Data Transformation Services node in Object Explorer under Management\Legacy, and select Migration Wizard.
- From Business Intelligence Development Studio. Create or open an Integration Services Project, right-click on the SSIS Packages node in Solution Explorer, and select Migrate DTS 2000 Package.
- From the command prompt. Start DTSMigrationWizard.exe from the C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder.
Select the Package Source
The Package Migration Wizard can migrate SQL Server 2000 packages that were saved to a SQL Server msdb database, to structured storage files, or to Meta Data Services. Packages that were saved to Visual Basic files cannot be migrated.
Important
SQL Server 2005 does not install support for Meta Data Services. The Package Migration Wizard can only access DTS packages saved to Meta Data Services if SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files are present on the computer.
If a package is located in an msdb database or in Meta Data Services, the user provides a server name, and the Package Migration Wizard authenticates the user by using Windows Authentication or SQL Server Authentication.
If a package is located in a structured storage file, you select the file to migrate. A structured storage file can contain definitions for many packages and can include multiple versions of a package. You can migrate some or all the packages and package versions in the file.
For more information, see Choose Source Location (Package Migration Wizard).
Select the Package Destination
The Package Migration Wizard can migrate packages either to an msdb database in an instance of SQL Server 2005, or to a .dtsx file. The .dtsx file is an XML representation of the package definition that is saved to the file system.
If the destination is SQL Server 2005, the user provides a server name, and the Package Migration Wizard authenticates the user by using Windows Authentication or SQL Server Authentication.
For more information, see Choose Destination Location (Package Migration Wizard).
Note
After migrating packages from within Management Studio, connect to the local instance of Integration Services in Object Explorer to see the migrated packages. If you selected SQL Server as the destination, the migrated packages will be visible under the MSDB node. If you selected a file system folder as the destination, right-click the File System node and select Import Package to display the migrated packages. After migrating packages from within BI Development Studio, the migrated packages are saved to the file system folder that you specified and added to the open project under the SSIS Packages node. DTS packages cannot be opened or edited in BI Development Studio.
Select Packages to Migrate
After the source and destination have been specified, the Package Migration Wizard lists the packages available in the msdb database, in Meta Data Services, or in the specified structured storage file. You can select the packages to migrate and update the default names that the Package Migration Wizard provides for the packages. You can also update the package names in Business Intelligence Development Studio or SQL Server Management Studio after the packages have been migrated.
For more information, see List Packages (Package Migration Wizard).
Provide Package Passwords
Packages developed by using earlier versions of SQL Server tools may be password-protected. You must supply the correct password before the wizard can access their package definitions and migrate the packages. If multiple packages share a password, you have to provide the password only one time. The Package Migration Wizard cannot continue until you have provided passwords for all password-protected packages. If no packages are password-protected, the wizard skips this step.
Log Migration Errors
You can enable the Package Migration Wizard to log errors that it encounters during migration.
For more information, see Specify a Log File (Package Migration Wizard).
View Migration Progress
The Package Migration Wizard migrates the selected packages one by one. If the wizard cannot migrate a package, it provides options to end the migration process for all remaining packages, or to skip the package and continue migration with the next package in the list. As each package is migrated, its migration status is updated.
For more information, see Complete the Wizard (Package Migration Wizard) and Migrating the Packages (Package Migration Wizard).
Check Migrated Packages
After migrating packages, the migrated packages should be opened in Business Intelligence Development Studio to check for issues, including validation failures. For example, some packages rely on preceding tasks within the same package to create or prepare data sources or data destinations, and may fail validation when these objects cannot be located. A validation failure of this sort can be resolved by setting the DelayValidation property to true on the task or other container object, or by setting the ValidateExternalMetadata property to false on the affected data flow component.
Note
After migrating packages from within Management Studio, connect to the local instance of Integration Services in Object Explorer to see the migrated packages. If you selected SQL Server as the destination, the migrated packages will be visible under the MSDB node. If you selected a file system folder as the destination, right-click the File System node and then select Import Package to display the migrated packages. After you migrate packages from BI Development Studio, the migrated packages are saved to the file system folder that you specified and added to the open project under the SSIS Packages node.
See Also
Other Resources
Migrating Data Transformation Services Packages
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
5 December 2005 |
|