Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DTS doesn't run on a 64-bit server Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 1:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 17, Visits: 90
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.

Post #458189
Posted Wednesday, February 20, 2008 1:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #458208
Posted Wednesday, February 20, 2008 2:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 17, Visits: 90
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.
Post #458230
Posted Wednesday, February 20, 2008 3:30 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 31, 2012 10:31 AM
Points: 633, Visits: 1,695
Here's the official word, straight from the horse's mouth:

http://support.microsoft.com/kb/810927
Post #458267
Posted Wednesday, February 20, 2008 11:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #458369
Posted Thursday, February 21, 2008 7:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 17, Visits: 90
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;)
Post #458539
Posted Wednesday, January 20, 2010 8:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:12 AM
Points: 368, Visits: 715
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."
Post #850967
Posted Monday, January 25, 2010 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 17, Visits: 90
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.
Post #853055
Posted Tuesday, January 26, 2010 3:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #853479
Posted Wednesday, March 24, 2010 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 11:11 AM
Points: 1, Visits: 6
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!
Post #888765
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse