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.