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.
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.