Working with DTS in SQL Server 2008

  • krakatoa66

    SSC Veteran

    Points: 278

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

  • pippo999

    SSC Enthusiast

    Points: 134

    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

  • krakatoa66

    SSC Veteran

    Points: 278

    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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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.

  • pippo999

    SSC Enthusiast

    Points: 134

    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

  • srinath Pedi

    Valued Member

    Points: 69

    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

  • webtomte

    Ten Centuries

    Points: 1125

    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.

  • jasonwhowell

    SSC Veteran

    Points: 286

    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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • jose-276033

    SSC Veteran

    Points: 219

    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

  • krakatoa66

    SSC Veteran

    Points: 278

    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.

  • Fozzie

    SSCrazy

    Points: 2107

    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:-)

  • srinath Pedi

    Valued Member

    Points: 69

    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