- The Scope of Things
- Available Migration Resources
- Some Migration Considerations
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Some Migration Considerations
When upgrading, SQL Server setup performs some installs and uninstalls of which you should be aware. The following are some key points you should understand before migrating your packages.
SQL Server Instances and Enterprise Manager
When upgrading to SQL Server 2005, SQL Server 2005 setup uninstalls certain deprecated components. Based on how you do your installs, it's possible that the Enterprise Manager environment will get uninstalled and take the package designer with it—essentially leaving your legacy DTS packages stranded with no way to edit them. To avoid this situation, you have a number of options, which are discussed in the following sections.
Installing an Instance
If you install SQL Server 2005 as an instance side by side with your SQL Server 2000 instances, all the SQL Server 2000 tools remain intact and you'll have access to the packages for editing and so forth. All packages that are scheduled to run with DTSRun and/or Agent continue to execute without issue.
Leaving One SQL Server 2000 Instance
This option is very similar to the previous one. Except instead of installing SQL Server 2005 as a different instance, you upgrade all SQL Server 2000 instances to SQL Server 2005 except one—leaving the tools intact. So long as there is a SQL Server 2000 instance on a machine, all the SQL Server 2000 tools you have used remain intact.
Upgrading and Installing Designer Components
When you select Integration Services for installation, setup also installs support for DTS packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. This happens as part of the SQL Server 2005 installation, but only the runtime components are installed, not the designer components.
For the design time components, Microsoft has created a special distribution called the Microsoft SQL Server 2005 DTS Designer Components. The Designer Components distribution replaces the components that get uninstalled when you upgrade your last instance of SQL Server 2000. If you want to upgrade all your SQL Server 2000 instances, this is the best and only option for recovering the DTS designer. To download the designer components, go to the download section of Microsoft.com and search for Knowledge Base article 339810.
Meta Data Services
Meta Data Services has been deprecated and is no longer supported as a package store. SQL Server 2005 does not install or use the Repository, so you cannot enumerate or open packages stored there. You can still access packages stored in Meta Data Services using DTSRUN, but not using the designer tools. The SQL Server 2000 Data Transformation Services (DTS) designer and the dtsrun.exe utility continue to support DTS packages that were saved to Meta Data Services.
SQL Server 2005 Integration Services supports the Repository only in the Upgrade Advisor and the Package Migration Wizard, and 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 Upgrade Advisor can scan and the Package Migration Wizard can migrate DTS packages that were saved to Meta Data Services. When the Repository files are not present, the Upgrade Advisor can only scan and the Package Migration Wizard can only migrate DTS packages that were saved to SQL Server or to structured storage files.
The Integration Services Execute DTS 2000 Package Task cannot execute a DTS package that was saved to Meta Data Services. As a workaround, when the Repository files are present, you can create a DTS package that uses an Execute Package Task to execute the Meta Data Services package, save this new parent package to SQL Server or as a structured storage file, and execute the parent package from the Execute DTS 2000 Package Task.
Migrating Packages with Passwords
If the DTS packages that you need to migrate have been protected with passwords, you need to know the passwords to migrate them. The passwords are not retained, nor does the Migration Wizard change the package protection setting on the resulting IS package. You need to manually modify the IS package to enable passwords again.
Where Are My Packages?
One thing you should understand is that regardless of how you upgrade to SQL Server 2005—whether you install side by side with a separate instance, upgrade the last instance, or retain the last SQL Server 2000 instance—SQL Server setup keeps your packages intact. Depending on how you upgrade, the packages can be found in a different location.
File System
Packages remain on the file system undisturbed. You can migrate them with the Package Migration Wizard or load them up in the Execute DTS 2000 Package Task.
Install Instance
If you install SQL Server 2005 as an instance side by side with your SQL Server 2000 instances and you were storing your packages in SQL Server, you will still have the Enterprise Manager environment intact so the packages will be where they have always been, in the Local Packages node of the Data Transformation Services tree of Enterprise Manager. Figure 3.8 shows DTS packages stored there.
)
Figure 3.8 DTS packages remain in local packages
Because Management Studio can enumerate packages in SQL Server 2000 instances, you can also find the DTS packages in the Object Explorer in Management Studio under the Management tree, Legacy, Data Transformation Services node. Figure 3.9 shows the packages in the Legacy node.
)
Figure 3.9 DTS packages remain in the Legacy node