You CAN execute DTS packages from SQL Server 2005 64-bit

Posted in: Technical Track

All over the web I’d read that DTS packages could be stored on SQL Server 2005 64-bit, but not executed on this server. Workarounds I’ve seen range from creating SSIS packages with Execute DTS tasks, migrations to SSIS using the wizard or third party tools, and running the DTS Packages from a 32-bit server against the 64-bit target.

Recently (and much to my embarrassment after making that statement), a colleague demonstrated that this is not correct. DTSRun.exe can be found on SQL Server 2005 64-bit installations (although it might not be supported by Microsoft).

Upon investigating and testing on my own, I found that on the servers I checked, DTSrun.exe is located in: C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\.

I mocked up a simple DTS package that picks up a flat file, creates a table, and then imports the data into it with a transformation step. I saved it as a structured storage file, which I copied to the 64-bit server, generated a DTS Run line (F.Y.I. – DTSRunUI.exe is not located on these servers), then executed it from a command prompt on the 64-bit server. And it worked!

SSIS is a good platform with many noticeable improvements over DTS (also some new quirks), but if you need a workaround or just can’t justify a complete re-write of your DTS packages to your manager, this may be an option for you.

I tested some simple commands, (not the entire functionality) so I strongly recommend you test your packages before relying on this in a production environment.

Please note that DTSRun.exe is not included in SQL Server 2008 installations.

Interested in working with Chris? Schedule a tech call.

About the Author

Chris Presley loves order—making him a premier Microsoft SQL Server expert. Not only has he programmed and administered SQL Server, but he has also shared his expertise and passion with budding DBAs as SQL Server instructor at Conestoga College in Kitchener, Ontario. Drawing on his strong disaster-recovery skills, he monitors production environments to swiftly detect and resolve problems before they arise. A self-described adrenaline junkie, Chris likes tackling the biggest database problems and putting out the toughest fires—and hitting the road on his motorcycle.

1 Comment. Leave new

Thanks – it is so even with imported DTS pkgs.
I have about 20 old DTS packages I exported previously then imported into the Legacy-DTS area of my new SQL2005 64bit.
I can certainly run each package with the following cmd line in a stored proc:
SET @cmd = ‘dtsrun /Sdonc-SQL5 /Npkg_name /E /AgvStartDate:8=’ + CAST(@intStartDate AS VARCHAR(8))
exec master..xp_cmdshell @cmd
If using an ODBC data source, pay attention: the DTS pkg will only see 32-bit ODBC DSN data sources. Windows will only see/create 64-bit ODBC Data Sources. You need a way to access the 32-bit ODBC administration area and create separate 32-bit ODBC data sources.


Leave a Reply

Your email address will not be published. Required fields are marked *