SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS doesn't run on a 64-bit server


DTS doesn't run on a 64-bit server

Author
Message
Annie G.
Annie G.
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 95
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87763 Visits: 45272
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

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


Annie G.
Annie G.
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 95
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.
Erik Kutzler
Erik  Kutzler
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1697
Here's the official word, straight from the horse's mouth:

http://support.microsoft.com/kb/810927
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87763 Visits: 45272
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

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


Annie G.
Annie G.
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 95
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 dayWink
Ol'SureHand
Ol'SureHand
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 720
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."
Annie G.
Annie G.
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 95
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.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5826 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
cyberdude_ro
cyberdude_ro
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search