none
DTS in SQL Server Management Studio 2008

    General discussion

  • I'm running SQL Sever 2008 Management Studio (Developer Edition) on an XP machine.
    Unfortunately I still have to manage some DTS packages for SQL Server 2000, and wanted to do that without having to install SQL Server 2000 Enterprise Manager.

    In SQL Server 2008 Object Explorer, going to Management->Legacy->Data Transformation Services, I could see all the DTS packages, however right-clicking and selecting open gave me this 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)

    So these are the steps I followed that worked for me, and now I could open a design-time DTS package window from SSMS 2008:

    1- Download and isntall the latest Microsoft SQL Server 2005 Backward Compatibility Components (I know it says 2005 but that's what you need for SSMS 2008 as well.  Also make sure you get the latest release, the one in this link is for December 2008)

    http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en

    2- Download and install the latest Microsoft SQL Server 2000 DTS Designer Components:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en

    3- At this point try right-clicking on a DTS package in SSMS 2008 and select Open, if you still get the error, follow these steps that I got from the reference site below:

     To ensure that the DTS designer can be used in SQL Server Management Studio

    1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

    2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\1033 folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033 folder.

    Note that the second step is the RLL files (not DLL).  I also added "\1033" at the end of the paths shown on the reference site.

    (Reference site: http://msdn.microsoft.com/en-us/library/ms143755.aspx )

    Enjoy...

    Thursday, January 08, 2009 7:02 PM

All replies

  • I'm also trying to edit old DTS-packages from a SQL server 2008.
    I followed all the steps above , the improvement is that when ik click open package that I can see the package and its history but now I get the errormessage (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) when the editor is opened en after clicking OK I have an empty editor screen.

    So any advice is welkom...

    Thursday, November 05, 2009 2:00 PM
  • The last step of copying the files between the 'old' folder and the 'new' folder is what made the DTS Editor come alive and work for me.

     

    Thank you very much. I was about to give up completely...(NOT)

    Thursday, May 20, 2010 1:47 AM
  • Thank you worked perfectly.

     

    Tuesday, October 19, 2010 11:54 AM
  • Hmm - I've followed all those steps, and its still not working for me.

    Windows 7 64 bit

    SQL Server 2008 R2

    The funny thing is, this was actually working a few days ago (in sql server 2005), until I had the misfortune to install VB6 on my x64 machine.  Now its not working in either 2005 or 2008R2.   Have reinstalled SQL Server, backwards compatibility and the DTS components, followed the above, but still nothing.

    Wednesday, January 19, 2011 1:10 AM
  • Excellent information. Thank you. However, I followed the instructions to the letter. I have SQL Server 2008 SP2 Developer Edition installed on Windows 7 32-bit and I still cannot edit the DTS packages...getting the same error as mentioned at the top of the article. I think it has something to do with the fact that SQL Server 2000 is not supported on Windows 7 of any flavor. What a shame. I do not look forward to installing WinXP Mode in a Virtual Machine to get this to work.

    Unfortunately the Flat File processor in SSIS cannot handle legacy CSV files that I must transform. So I must resort to DTS 2000. The files are consistently formatted as noted by another poster in SqlServerCentral.com.

    "I feel your pain man...there is no official "standard" for CSV files...this is the closest thing out there but it's just an RFC: http://www.rfc-editor.org/rfc/rfc4180.txt

    Your file conforms to section 2, unfortunately the SSIS development team responsible for flat file processing did not adopt this document when developing the CSV file handling piece. That said, onward..."

    It's a shame that DTS 2000 and Excel 2007 and Access 2007 can all open these files flawlessly, but SSIS chokes on a comma that is within " delimited character fields. It seems to me that those other products are Microsoft's also. Their export facilities even produce the type of CSV I am trying to process. An example...

    13243,"String 1","149,000.00",8907,...

    SSIS will choke on the 3rd column "149,000.00" and produce a column break at the comma. Any human can see that the data as presented is valid and consistent. Oh well, I guess it's not important to process a data format that is used commonly around the globe by millions of people. The SQL Server gurus will will say..."But the 149,000.00 should be presented as a number." I'm stuck with it. What about free-form text? "Then Joe said,""How about a game of poker?""". SSIS chokes here too. Those guys in the back room have lost touch with humanity. LOL Oh Well...progress I guess.


    Thursday, April 21, 2011 12:01 AM
  • I've actually been round and round with this as well.  Followed the instructions above and it worked like a champ on Server 2003 and XP, but Not on Windows 7 64 bit.  It seemed to be a matter of doing it all in the right order and rebooting where necessary to fix it in my case.  The below order finally worked for me on Windows 7 x64.

    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/downloads/en/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&pf=true

    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

    Hope this helps somebody.  What a pain.

    Thursday, May 12, 2011 2:24 PM
  • Current System:

    Windows 7 64bit

    sql server 2008 R2 64 bit

    The following intructions worked, The order is very specific because i have performed these steps prior without sucess.  This is the complete guide. Thanks iphillips

    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/downloads/en/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&pf=true

    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

     

    Monday, October 24, 2011 7:37 PM
  • Wow, worked like a champ. Thanks for sharing.
    Wednesday, February 08, 2012 2:35 AM
  • When I go to %ProgramFiles(x86)%\Microsoft SQL Server\100 there is NO Tools folder.

    Using Sql server 2008 R2 64bit.


    • Edited by Zoomy22020 Friday, November 01, 2013 4:23 PM
    Friday, November 01, 2013 4:23 PM
  • thank you for the greate artical, the problem is it dosnt work with me, i am using window 8 64bit and also i can find folder Resources under %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033 folder.

    any suggestion

    Tuesday, June 17, 2014 1:07 PM