Unable to open DTS package in SQL 2008 management studio

I recently worked on an issue where my customer was not able to open DTS package in SQL server 2008 management studio.

The package was migrated from SQL server 2000 to SQL server 2008 R2. He wanted to run/edit/open that from SQL server 2008 management studio.

The error which he was facing was:

Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)

Based on the error message, We Installed the : – SQLServer2005_BC*.msifrom the link : – http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988

After installation, again tried to open the package but then a new error occurred:

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)

Based on the error message installed the DTS designer component (SQLServer2005_DTS.msi) from the link: –

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988

But still the same error :

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)

Finally, checked the PATH in environment variable and found the location: C:Program Files (x86)Microsoft SQL Server80ToolsBinn  to be after C:Program Files (x86)Microsoft SQL Server110DTSBinn which means that the newly installed components will not be picked up.

Changed it otherways round  I mean copied C:Program Files (x86)Microsoft SQL Server80ToolsBinn in the begining of the PATH variable output and then restarted the SQL enterprise management studio.

 

After that when tried to open the package , it was successful.

Here is another post which talks about similar issue :- http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-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-ssms.aspx#10162222

HTH!