Weird SSIS Error (Process Exit Code 255)

Question

I'm having an issue with an SSIS job I recently created that is breaking with a strange error.

Background Info

This is an SSIS package (.dtsx) that is being run with a SQL job (SQL Server 2008, job created with SSMS) using the type SQL Server Integration Services Package as you would expect. The job runs fine in our development environment, but not in our test environment. The package uses a configuration file (.dtsConfig) to maintain the connection strings for the different environments. What the package is actually doing is pretty simple; it truncates some staging tables, and then fills then with data pulled from an Oracle database using 8 data flow tasks in a sequence container, with each task breaking down into Source (Oracle) -> Data Conversion (for unicode strings) -> Destination (SQL Server). Because of the Oracle connection that needs to be made, the package contains the user/pass for that connection and as such has a protection level of 'EncryptSensitiveWithPassword'to maintain that. The server that this job is being run on is running Windows Server 2008 R2 SP1. I believe thats it as far as background info, but if I missed anythign let me know.

The Actual Error

This is the only useful message in the broken job's job history:

The step did not generate any output. The return value was unknown. The process exit code was 255. The step failed.

As you can see, not very helpful. The real kicker is that when I run this job manually using DTEXEC on the server, it runs fine. This leads me to believe that it may be a permissions issue with the sqlagentadmin or something, but everything that I look at seems fine. I checked out what was happening using procexplorer and it was running the correct DTEXEC command. The application event viewer does pop up with this though (so maybe its an oracle driver issue):

Faulting application name: DTExec.exe, version: 2007.100.5500.0, time stamp: 0x4e7b1ed6
Faulting module name: OraClient11.Dll, version: 11.2.0.1, time stamp: 0x4bb1dcae
Exception code: 0xc0000005
Fault offset: 0x0000000000057106
Faulting process id: 0x2d94
Faulting application start time: 0x01cd66b0d360164d
Faulting application path: C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
Faulting module path: c:\app\product\11.2.0\client_1\bin\OraClient11.Dll
Report Id: 118a2173-d2a4-11e1-b1ec-001517955142

I've looked around the internet for hours and I've tried some things. I changed the Delay Validation property to 'True' on all data flow tasks. I've tried setting up the job with the integrated SSIS GUI, and with the operating system option to just run the dtexec, both fail.

Any suggestions? Sorry for the wall of text, but I just wanted to make sure I covered everything.

Thanks!

Answer 1

I got the following error :

' The step did not generate any output . The return value was unknown . The process exit code was 255. The step failed. '

To solve my problem , I checked for privileges for reading the source data.

I did not have the right privileges. Got the correct privileges and restarted my job solved the problem .

I could suggest that the API between SSIS as the executing task and in my case DB2 data source provider should assign fault with better information.

Problem solved by getting reading privileged to source data.