GilaMonster (2/20/2008)
...there is no 64 bit solution to this problem.
That is correct. There is a 32-bit DTS Legacy solution though.
If you have SQL Server 2005, you can rewrite all the DTS packages in SSIS (not a trivial task) and SSIS does run on 64 bit
For completeness sake: you can import legacy DTS packages (from SQL2000) into SQL2005 64-bit and you can make them run (install the backward compatibility and DTS designer components from MS).
In my case, I have to do that because SSIS using 64-bit IBM OLEDB drivers to DB2 will copy 8.5 million records @ cca 90 byte each in 12 minutes, but DTS using IBM ODBC 32-bit drivers do the same job in only 4 minutes. Extrapolating over the 30 big tables I import nightly, that would be 4 extra hours or so ...
I can certainly run each package with the following cmd line in a stored proc:
SET @cmd = ‘dtsrun /Sserver_instanceName /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 otherwise you cannot even edit the imported DTS packages - you get a silly error "Hresult 0x80004005 Unexpected error ... error result returned without an error message."