February 20, 2008 at 1:05 pm
Hello cyber friends,
I have a DTS that runs fine on a 32 bit server, but it fails on the new 64bit server which has SQL Server 2000 Enterprise Edition and SP4. The DTS copies a txt file into a table and then FTP some other file to a Window's folder.
When I explicitly include the DTSRun exe path of: C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\DTSRun /S
the following error is issued:
Executed as user: OCPS\43544. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
When I use the: DTSRun /S
the following error is issued:
Status: Executed as user: OCPS\43544. The process could not be created for step 1 of job 0xC519AF496CB01449BA3A4682841B4903 (reason: The system cannot find the file specified). The step failed.
I found an article depicting both errors, http://support.microsoft.com/?kbid=274716, but it refers to SQL Server 7.0.
I tried putting the DTSRun /S in a text file and saved it as a .bat then ran it from the command prompt, but received the same 'Invalid class string' error.
Any and all information is greatly appreciated, thanks.
Annie G.
February 20, 2008 at 1:41 pm
As far as I know, there's no 64 bit compile of DTSRun.
I ran into exactly the same issue when we moved our app onto an Itanium. We had to get a second server (32 bit) to run the DTS packages on
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2008 at 2:34 pm
That's exactly what we are doing, but they don't want to accept the work around and insist that I come up with the 64-bit solution!!!!
Thanks for your input.
Annie G.
February 20, 2008 at 3:30 pm
Here's the official word, straight from the horse's mouth:
February 20, 2008 at 11:13 pm
Typical management. Tell them straight, there is no 64 bit solution to this problem.
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
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2008 at 7:22 am
Thank you all for your prompt response. This site is my fountain of knowledge; I'll start my inevitable new mission, to rewrite the DTS in SSIS.
'till next hurdle... have a great day;)
January 20, 2010 at 8:04 pm
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."
January 25, 2010 at 8:50 am
hello there... it's beren a long time... but better late than never...
THANK YOU so very much for the follow up.... we'll will resucitate this issue and look into, will keep you posted, thanks again...
Annie G.
January 26, 2010 at 3:13 am
I am sure I have got native DTS packages to run on SQL Server 2005 64-bit, so it may be worth building a test environment with this configuration to see if it works for you. You need the Backward Compatibility component and the DTS Designer component from the Feature Pack to get this to work.
However, DTS is not supported on 64-bit SQL Server 2000. If you dig into the documentation, there are some other things that do not work on 64-bit SQL 2000 that work on 32-bit SQL 2000.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 24, 2010 at 3:20 am
Another 2 cents worth of advice after spending almost 2 days trying to figure out a solution:
My scenario was quite hectic since I had to to a migration that involved several environment changes:
Windows Server 2003 -> Windows Server 2008
SQL Server 2000 -> SQL Server 2005
32 bit -> 64 bit
I had to move a web app written in classic ASP to a 64 bit box and the app was executing DTS packages from inside a page. The error was "ActiveX Component can't create object: DTS.Package". I finally found about the limited/complicated support for DTS on 64 bit systems and I figured out that since there is no 64 bit version of the DTS dlls, clearly the DTS.Package was not to be found registered in COM where it was being looked for.
I had all the DTS 2000 components and backwards compatibility components installed, I was able to edit and run the packages under Mgmt Studio just fine, it was only the programmatic access that didn't work. The solution was pretty straightforward then: convince the IIS worker process to run in 32 bit mode so the DTS.Package was searched in the correct place in registry. Now I can only tell about IIS7 but I guess for older IIS versions solutions can be easily found. First, locate the application pool under which the web app is running, go to advanced settings and then make sure the "Enable 32-bit applications" setting is on "True". This way the w3wp process will run in 32 bit mode (easily verifiable in Task Manager where you will find a "w3wp.exe *32" entry) and the vbscript in the ASP page will look in the 32bit area of the registry for the DTS.Package COM object.
Hope this helps!
March 24, 2010 at 4:13 pm
I had all the DTS 2000 components and backwards compatibility components installed, I was able to edit and run the packages under Mgmt Studio just fine, it was only the programmatic access that didn't work. The solution was pretty straightforward then: convince the IIS worker process to run in 32 bit mode so the DTS.Package was searched in the correct place in registry. Now I can only tell about IIS7 but I guess for older IIS versions solutions can be easily found. First, locate the application pool under which the web app is running, go to advanced settings and then make sure the "Enable 32-bit applications" setting is on "True". This way the w3wp process will run in 32 bit mode (easily verifiable in Task Manager where you will find a "w3wp.exe *32" entry) and the vbscript in the ASP page will look in the 32bit area of the registry for the DTS.Package COM object.
Hope this helps!
IIS7 can run in both x86 and x64 mode so your .asp application needs to run in x86 mode. In Windows 2003 IIS6 can only run one mode so if you need to run .asp you need to run IIS6 in x86 that is 32bits mode.
Kind regards,
Gift Peddie
September 9, 2010 at 2:37 pm
I had all the DTS 2000 components and backwards compatibility components installed, I was able to edit and run the packages under Mgmt Studio just fine, it was only the programmatic access that didn't work.
That was the same for us as well. I was able to export a DTS pack from a SQL 2000 server, import it into SQL 2008, edit it with DTS 2000 Designer, run it from DTS 2000 Designer, run it from a command line, etc ... the only place it didn't work was running it with xp_cmdshell:
exec xp_cmdshell 'dtsrun /S<servername> /U<username> /P<userpassword> /N<dtspackname>'
The error returned was:
'dtsrun' is not recognized as an internal or external command ...
Since it worked everywhere else we assumed the path was the issue and tried updating the sql and agent service account environment variable PATH. Even that didn't work ... which meant the sql path isn't the same as the sql service account path? So we ran the dos command to display the path that was actually being used:
exec xp_cmdshell 'set PATH'
which displayed the path used by sql server. This proved to be completely different than the service account (user) paths, which lead to more research, which lead to finding out that sql gets it path variables from the registry. So we eventually found the dts path in the registry and added the old path in front of the new one:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup\SqlPath
before:
C:\Program Files\Microsoft SQL Server\100\DTS
after:
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS
Make sure to restart the service so the changes take effect.
October 7, 2010 at 8:19 pm
A simple solution to run DTS2000 in 64 bit environment is to install SQL2005 or SQL2008 create a new SSIS package that simply executes the DTS2000 package using the Execute DTS2000 package task. Change the 64bit Runtime = False on the newly created package. This solution saved our upgrade project when going from SQL2000 to SQL2008.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply