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!

Advertisements

5 thoughts on “Unable to open DTS package in SQL 2008 management studio

  1. This is a great article, thanks. It has really helped me out. How ever I need to install this on cluster that is running a sql 2005 & SQL 2008r2 instance. Would ths work?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s