Skip to main content

[SQL Server] Configuring DTS Designer to run with SQL Server 2008

Chris Alberts
  • Updated

Issue:

With each new release of SQL Server, Microsoft has made it harder to implement DTS support. The culmination of this is that there is no support for running DTS packages in SQL Server 2012. However, SQL Server 2005 and SQL Server 2008/R2 can be made to run and edit DTS packages. Configuring this in SQL Server 2005 is well documented online and very straightforward. This is not the case with SQL Server 2008/R2. The following article from the BOA Knowledge base provides detailed steps.

When installing Backward compatibility and DTS editing/run function in SQL 2008 SSMS on 64bit OS, the advised Microsoft procedure for enabling DTS functions, will not work. Symptom is that the popup message appears that DTS 2000 design components need to be installed, regardless of whether the installation procedures have been followed.

Resolution:

We were able to solve this issue by following these instructions to the letter.

Perform the following steps on the web server.

  1. Remove any currrent installations of 2005 backwards compatibility and 2000 DTS designer
  2. Reboot if starting at Step 1
  3. Install SQLServer2005_DTS.msi from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en&pf=true
  4. Install SQLServer2005_BC.msi (x64 package) from http://www.microsoft.com/en-us/download/details.aspx?id=16177
    NOTE: For Windows 7 it seems to be important to use the DTS designer from the first link (2005 feature pack) and the Backwards compatiblity from the second link (2008 feature pack), which is what is specified above.
  5. Copy SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL
    from: %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder
    to: %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
  6. Copy RLLs SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL
    from: %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\1033
    to: %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033
  7. Reboot.

In some cases you may still receive an error when you open a legacy package. If so, perform the following steps:

  1. Make sure all the SQL Server Management Studio (SSMS) sessions to the server are closed. 
  2. Go to Control Panel --> System --> Advanced --> Environment variables.
  3. Update the PATH environment variable in such a way that the path “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\” appears before the “C:\Program Files\Microsoft SQL Server\100\...” path in the list. If SQL Server is installed in 64-bit server, the folder might be \Program Files (x86)\ instead of \Program Files\. Use the appropriate path for your environment. 
  4. Launch SSMS and open a DTS package from Legacy \ Data Transformation Services. 
  5. You will get some errors. Click OK. The package opens but the icons can’t be seen. 
  6. Just hover the mouse over the icons till you notice icons as white boxes. 
  7. Exit SSMS. 
  8. Again launch SSMS and open the same DTS package from Legacy \ Data Transformation Services. 
  9. Package should appear properly. 

Taken from: http://blogs.msdn.com/b/ramoji/archive/2008/10/16/how-to-open-dts-packages-in-sql-server-2008.aspx

Related articles