SSIS package fails as Job on 64 bit Sql Server 2005 (Enterpr)

Question

SSIS package fails as Job on 64 bit Sql Server 2005 (Enterpr)

Yuri55

SSCrazy Eights

Points: 8449
More actions
March 14, 2007 at 9:41 pm

#116251

I read some posts regarding same issue but could not find solution. Package (export data from table into xls file) is OK when run as package bur fails with error "The package execution failed." when run as Job. I tryed already Proxy, changing ProtectionLevel, etc- same error. When I created another test package to transfer data from table to another Sql Server- no problem, job works like a charm. On another Sql server (32 bit 2K5) this job (running SSIS package data to file) works already some months with no problem.
It seems to me that 64 bit Edition is the reason for our problem but what confuses me that error arises only when transfering data to xls file (not table to table). Any comments? Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 1

When you run it as a package, is that from BI Studio on your workstation (32bit)?

It's not as straightforward as just creating a job to get it to work on 64 bit. SSIS can only work with excel files on a 64 bit system when running under the Windows On Windows 32 bit emulation environment. They say you can call the 32 bit executable to run the job, and you should be fine (I have not personally tried this). Here is a relevant quote from MS:

"Neither ODBC nor Microsoft Office data (Office Excel, Office Access, Jet 4.0, ACE, and so on) is supported in 64-bit SSIS scripts. You can, however, run scripts that use these types of data by using the 32-bit command line program on any 64-bit server." [1]

We ran into this, and other issues when running SSIS on 64 bit. We finally just set up a 32 bit server and now run everything from there.

[1] http://msdn2.microsoft.com/en-gb/library/bb332055.aspx (it's buried deep in the document)

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 2

Found some implementation details:

"If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step."

http://msdn2.microsoft.com/pt-br/library/ms141766.aspx

Have fun!

Ken

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 3

Thanks for your answers, Ken

First of all, I run Package from Management Studio- IntegrationServices-RunPackage. And it's OK. Second, I read regarding selecting job type of Operating system but have no idea how this command pointed to 32 bit version looks like. Could you please provide me with this info. Thanks one more time

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 4

Found this on MS TechNet Forum and it works for me:"To do this, edit the SSIS step in Agent. Copy the text from Command Line tab. Switch step to use CmdExec subsystem instead of SSIS subsystem. Specify 32-bit DTEXEC as application ("C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" - including quotes) and add the parameters you've copied from Command Line tab.". That means for 64-bit version we should point to 32 bit DTExec.exe. That's it.