I am having a problem with an SSIS job that I recently created that breaks with a strange error.
Background Information
This is an SSIS package (.dtsx) that runs with an SQL job (SQL Server 2008, a job created using SSMS) using the SQL Server Integration Services package, as you would expect. The job works great in our development environment, but not in our test environment. The package uses a configuration file (.dtsConfig) to support connection strings for different environments. What the package really does is pretty simple; it trims a few staging tables and then populates the data extracted from the Oracle database using 8 data flow tasks in the sequence container, each task being divided into Source (Oracle) → Data Transformation (for Unicode strings) → Destination (SQL Server). Due to the Oracle connection that needs to be made, the package contains the user / password for this connection and, as such,has a "EncryptSensitiveWithPassword" security level to support this. The server that runs this task is running Windows Server 2008 R2 SP1. I believe this is for reference information, but if I missed anythign let me know.
Actual error
This is the only useful message in the broken work history:
This step did not generate any output. The return value was unknown. The process exit code was 255. The step failed.
As you can see, this is not very useful. The real kicker is that when I run this task manually using DTEXEC on the server, it works fine. It makes me think it might be a sqlagentadmin permission issue or something, but everything I look at seems fine. I checked what happens with procexplorer and it executed the correct DTEXEC command. An application event viewer pops up with this though (maybe this is a problem with the oracle driver):
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
, -. " " "" . SSIS, dtexec, .
? , , .
!