You may experience problems when you try to run a SQL Server 2000 DTS package after you upgrade all the instances of SQL Server 2000 to SQL Server 2005

Symptoms

Consider the following scenario. You upgrade all the instances of Microsoft SQL Server 2000 to SQL Server 2005. Then, you try to run a SQL Server 2000 Data Transformation Services (DTS) package. In this scenario, you may experience the following problems:
  • When you try to run the SQL Server 2000 DTS package by using the dtsrun command prompt utility, you receive the following error message:
    'dtsrun' is not recognized as an internal or external command, operable program or batch file.
  • When you try to run the SQL Server 2000 DTS package in a scheduled SQL Server Agent job, you receive an error message that resembles the following:
    The process could not be created for step 1 of job 0x551C5F1D36265B4DA67502C6E2E549AF (reason: The system cannot find the file specified). The step failed.
  • When you try to edit the SQL Server 2000 DTS package by using SQL Server Management Studio or by using the Execute DTS 2000 Package task in Business Intelligence Development Studio, you receive an error message that resembles the following:
    SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)
  • Other applications that depend on SQL Server 2000 DTS functionality do not work correctly. For example, if you use a third-party scheduling tool or if you call DTS package execution from a custom interface, the applications do not work correctly.

    Note You do not experience a consistent set of symptoms when the applications do not work correctly.
When you experience these problems, no DTS log data is created.

Cause

This problem occurs because the applications that are used to run the SQL Server 2000 DTS package cannot find the SQL Server 2000 DTS runtime.

Support for SQL Server 2000 DTS is installed automatically when you install SQL Server 2005 Integration Services. However, some of the registry settings and some of the environment settings that SQL Server 2000 DTS requires are deleted when you upgrade the last instance of SQL Server 2000. This behavior is caused by the code in SQL Server 2000 Setup that uninstalls the SQL Server 2000 components. At the end of the upgrade, the directory that contains the SQL Server 2000 DTS binaries is no longer present in the PATH system environment variable. Additionally, a registry entry that directs applications to these binaries is deleted. Therefore, when you try to use SQL Server 2000 DTS functionality at a command prompt or as part of an application, the attempt fails.

Resolution

To resolve this problem, use one of the following methods to repair the SQL Server 2005 Backward Compatibility Components.

If you use method 1, method 2, or method 3, we strongly recommend that you install the latest SQL Server 2005 service pack when you are finished. If you cannot do this, we recommend that you reinstall the SQL Server service pack that you were running before you repaired the SQL Server 2005 Backward Compatibility Components.

Additionally, if the problem involves SQL Server Agent, you must restart the SQL Server Agent service after you use one of these methods.

Method 1

Repair the SQL Server 2005 Backward Compatibility Components by using Add or Remove Programs in Control Panel. To do this, follow these steps:
  1. In Control Panel, open Add or Remove Programs.
  2. In the Currently installed programs list, click Microsoft SQL Server 2005 Backward compatibility.
  3. Click Change.
  4. In the Microsoft SQL Server 2005 Backward Compatibility Setup Wizard, click Next.
  5. On the Program Maintenance page, click
    Repair, and then click Next.
  6. To repair the SQL Server 2005 Backward Compatibility Components, click Install.

Method 2

Repair the SQL Server 2005 Backward Compatibility Components by running the redistribution file on the SQL Server 2005 setup media in repair mode. To do this, follow these steps:
  1. On the SQL Server 2005 setup media, locate the Servers\Setup folder, and then double-click the
    SQLServer2005_BC.msi file.
  2. In the Microsoft SQL Server 2005 Backward Compatibility Setup Wizard, click Next.
  3. On the Program Maintenance page, click
    Repair, and then click Next.
  4. To repair the SQL Server 2005 Backward Compatibility Components, click Install.

Method 3

Repair the SQL Server 2005 Backward Compatibility Components by running a command for unattended installation. To do this, follow these steps:
  1. Obtain the product code of the installed SQL Server 2005 Backward Compatibility Components. To do this, follow these steps:
    1. Start Registry Editor.
    2. Locate the following registry subkey:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
    3. Press CTRL+F.
    4. In the Find what box, type
      Microsoft SQL Server 2005 Backward compatibility, and then click Find Next.
    5. Locate the parent registry subkey of the registry entry that is found. The product code is the name of this registry subkey. For example, the product code may be ABB9B19D-3D5A-4162-87B2-242C49E63D14.

      The product code is also the value of the UninstallString registry entry in this registry subkey.
  2. Obtain the full path of the SQLServer2005_BC.msi file.

    Note For SQL Server 2005 32-bit installations on the 32-bit subsystem (WOW64) of a 64-bit server, use the SqlServer2005_BC_x64.msi file that is located on the original setup media.
  3. At a command prompt, run one of the following commands:
    • If you want the status to be displayed during setup, run the following command:
      msiexec /qb /fomus "ProductCode" | "FullPath\SQLServer2005_BC.msi" /lv "LogFile"
    • If you do not want the status to be displayed during setup, run the following command:
      msiexec /qn /fomus "ProductCode" | "FullPath\SQLServer2005_BC.msi" /lv "LogFile"
    Notes
    • ProductCode is a placeholder for the product code of the installed SQL Server 2005 Backward Compatibility Components. The product code is different for each release, for each language, and for each installation of the SQL Server 2005 Backward Compatibility Components.
    • FullPath is a placeholder for the full path of the SQLServer2005_BC.msi file.
    • LogFile is a placeholder for the path of the log file. You can omit the /lv switch if you do not need a log file.

Method 4

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows


Repair the SQL Server 2005 Backward Compatibility Components by manually updating the registry and the system path. To do this, follow these steps:
  1. Create a .reg file that contains the following text.
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup]
    "SQLPath"="C:\\Program Files\\Microsoft SQL Server\\80\\Tools"
  2. To update the registry, double-click the .reg file that you created in step 1.
  3. In the PATH system environment variable, add the following path.
    C:\Program Files\Microsoft SQL Server\80\Tools\Binn
Note In steps 1 and 3, you may have to update the path to reflect the actual location of the 80\Tools folder for the installation.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

This problem does not occur if you install a new instance of SQL Server 2005 together with the existing SQL Server 2000 installation. However, this problem occurs if you uninstall all the instances of SQL Server 2000 after you do this. In this case, the methods to resolve the problem are the same as the methods in the "Resolution" section.

If you have multiple instances of SQL Server 2000 installed, this problem occurs only when you upgrade the last instance of SQL Server 2000. When you upgrade all the instances of SQL Server 2000, SQL Server 2005 Setup calls a part of the code that uninstalls SQL Server 2000. This code deletes the relevant settings for SQL Server 2000 DTS.

This article discusses the functionality of running a SQL Server 2000 DTS package in the SQL Server 2005 Integration Services environment. By design, you can directly edit a SQL Server 2000 DTS package by using SQL Server 2005 Integration Services. You can run a SQL Server 2000 DTS package and then migrate the package to the SQL Server 2005 Integration Services environment. However, if you want to edit a SQL Server 2000 DTS package and to leave it in the SQL Server 2000 DTS package format, you must install the SQL Server 2000 DTS Designer Components.

To download the SQL Server 2000 DTS Designer Components, visit the following Microsoft Developer Network (MSDN) Web site:
Propriedades

ID do Artigo: 919131 - Última Revisão: 16 de jun de 2008 - Revisão: 1

Comentários