Working with DTS in SQL Server 2008

  • Comments posted to this topic are about the item Working with DTS in SQL Server 2008

  • Hello

    thanks for the interesting article.

    I started the procedure to install and activate DTS support on 2008 server, but even if I have

    - Integration Services.

    - Client Tools Backward Compatibility.

    installed on my SQL2008, when I launch SQLServer2005_BC for my system I get the error

    "Installation of Microsoft SQL Server 2005 Backward compatibility

    failed

    because a higher version already exists on the machine. To proceed,

    uninstall the higher version and then run Microsoft SQL Server 2005

    Backward compatibility setup again."

    How can I solve it?

    Thanks

    Paolo

  • Hi Paolo,

    Did you look for SQL Server 2005 Backward Compatibility in Add/Remove Programs? If its there try unistalling it and then try your install again.

    Dale

  • There is also an extra permissioning step required in order to be able to execute DTS packages in SQL 2008. All SQL Server logins which need to run a package need to be users in the msdb database with membership of one of the SSIS roles. We use db_ssisoperator.

  • krakatoa66 (2/15/2010)


    Hi Paolo,

    Did you look for SQL Server 2005 Backward Compatibility in Add/Remove Programs? If its there try unistalling it and then try your install again.

    Dale

    Yes checked before posting. No prev installation. I googled about this issue but found no definitive post or article about it.

    Thanks

  • Darren Green has created a wonderful tool which is free to use, it is calledDTSBackup . We have been using this tool for more than six years, for moving DTS packages from one system to another. This tool also enables to move the packages across different versions of SQL Server. We have used it on SQL Server 2000; SQL Server 2008 and between them.

    The tool is very simple to use and it moves packages under few minutes. In our environment when we moved about 100 packages; about 20 them were large; it was completed under 5 minutes.

    Enjoy

  • There is an excellent software that can convert DTS packages to SSIS packages from a company called pragmatic works. I have done this recently and it works. I had to do some code adjustments (cast and stuff) on one column only. Otherwise it worked superbly.

    So now I do not have to worry about DTS packages anymore as they are depreceated by Microsoft.

  • The newer runtime dtexec.exe doesn't run DTS 2000 packages natively. It runs SSIS 2005/2008 packages with the [Execute DTS 2000 Package Task] embedded as a task in the control flow.

    The easier runtime to run DTS 2000 packages is DTSRun.exe.

    Which can be installed via the Backward Compatibility SQLServer2005_BC*.msi or from the latest feature pack download for SQL 2008.

    http://msdn.microsoft.com/en-us/library/ms143755.aspx

    You should also mention that DTS 2000 is deprecated, and might not be supported in future releases of SQL Server. http://msdn.microsoft.com/en-us/library/ms403408.aspx

    Most of these topics are covered in MSDN

    http://msdn.microsoft.com/en-us/library/bb500440.aspx

  • Interesting Article. Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi,

    we had the same situation and we did the same. Some of them where using activex so it was abit of a challenge to makes them work again 🙂

    Thanks for sharing

    Jose

  • Yes checked before posting. No prev installation. I googled about this issue but found no definitive post or article about it.

    Thanks

    I have read online that it may not always appear in Add\Remove programs but to be completely sure that there is no registry entry you can download and install Windows

    Installer Cleanup Wizard. There's a good chance this will pick it up.

  • Srinath what a great call you gave about "DTS Backup 2000"!!!

    First off I couldn't get the DTS package stuff working on one of the machines. Just wouldn't work. Then found out you need the enviromental variables (path variable to be exact) in the correct order - this may negate having to copy files between the directories as proposed by krakatoa66 in the article.

    i.e references to the sql 2000 (80) should appear before sql 2005/2008 (90/100).

    Now I'd tried every trick in the book to open DTS packages on 2008, couldn't find a thing to work for certain packages.

    I'd reinstalled the DTS components, repaired them, and all sorted. I tried following this blog as well:

    http://blogs.msdn.com/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

    Got nowhere. Was going to ask for the dll file mentioned, but turned out I had it on my machine and that too couldn't open certain packages. I think it may be a case of packages created pre SQL 2000 SP3/4.

    Then tried the DTS Backup 2000 program, and hey presto, worked a ruddy charm.

    Anyway, Srinath, that tool made all the difference! And obviousily thanks to Daren and Allan at SQLDTS.com!

    Kevin

    :-):-D:hehe::cool::hehe::-D:-)

  • You are most welcome!!!.

    I understand your frustation!!!. Till this day, I couldn't configure this on my PC but my DBA did configure the DTS package stuff on both of our servers....

    Fozzie (5/13/2010)


    Srinath what a great call you gave about "DTS Backup 2000"!!!

    First off I couldn't get the DTS package stuff working on one of the machines. Just wouldn't work. Then found out you need the enviromental variables (path variable to be exact) in the correct order - this may negate having to copy files between the directories as proposed by krakatoa66 in the article.

    i.e references to the sql 2000 (80) should appear before sql 2005/2008 (90/100).

    Now I'd tried every trick in the book to open DTS packages on 2008, couldn't find a thing to work for certain packages.

    I'd reinstalled the DTS components, repaired them, and all sorted. I tried following this blog as well:

    http://blogs.msdn.com/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

    Got nowhere. Was going to ask for the dll file mentioned, but turned out I had it on my machine and that too couldn't open certain packages. I think it may be a case of packages created pre SQL 2000 SP3/4.

    Then tried the DTS Backup 2000 program, and hey presto, worked a ruddy charm.

    Anyway, Srinath, that tool made all the difference! And obviousily thanks to Daren and Allan at SQLDTS.com!

    Kevin

    :-):-D:hehe::cool::hehe::-D:-)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply