Working with DTS in SQL Server 2008

Question

Post reply

Working with DTS in SQL Server 2008

krakatoa66

SSC Veteran

Points: 278
More actions
February 14, 2010 at 10:22 am

#216760

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

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

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

pippo999 SSC Enthusiast Points: 134 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 1135 More actions · Answer 6

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 More actions · Answer 7

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: 281334 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 2222 More actions · Answer 11

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 More actions · Answer 12

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