0xc0202009 error when running job in sql server agent

I get 2 errors when I run a job in sql server agent 2005 0xc0202009 and 0x80040E2. I cannot see why I am getting these errors, when I run the job with dtexec it is successful. Also when I run it in the debugger or run the executeable it is successful. But when I try to run it with the agent it fails. Any Ideas would be greatly appriciated.

thank you
Joey Kushinski

[379 byte] By [Joeykushinski] at [2008-1-7]

«« Abstraction
»» WSHttp-WCF with certificates

# 1

Errors that only show up when running through Agent are often caused by security issues with the account the Agent is running under. It would be helpful if you turned on logging and reporting the complete error messages. Most of us don't have the error codes memorized

jwelch at 2007-10-2 >

top of Msdn Tech,SQL Server,SQL Server Integration Services...

# 2

I figured it out....for any one else having this problem. If you are running in sql server 2005 64-bit edition your oledb connection could only be supporting a 32-bit driver. you can change sql server agent to run in 32-bit mode since it runs by default in 64-bit. To do this in you job edit the step to type operating system (cmd exec).

fixed my problem!!!!

Joeykushinski at 2007-10-2 >

# 3

I also get this error, however, running the job in 32 bit (which works) is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory in 64 bit.

I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:12:43 AM
Error: 2007-08-15 11:12:45.63
Code: 0xC0202009
Source: Load Dimension Data Connection manager "ora"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2007-08-15 11:12:45.65
Code: 0xC020801C
Source: CopyCustomers CustomerSource [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2007-08-15 11:12:45.65

The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd:

"c:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\Packages\Load Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Any help would be greatly appreciated. Thanks!

HappyCow at 2007-10-2 >

# 4

I have managed to find two work arounds, but they are quite ugly:

1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package.

2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:\Packages\Load Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'

Both work and use the 64 bit dtexec. Not that elegant though.. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1?

HappyCow at 2007-10-2 >

# 5

For what it's worth, I also ran into lots of problems getting things to work, but eventually found a solution by explicitly calling the 32-bit version of dtexec from TSQL like this:
exec xp_cmdshell 'C:\PROGRA~2\MICROS~2\90\DTS\Binn\dtexec /DTS "\MSDB\SSIS_Server_Table" /CHECKPOINTING OFF /REPORTING V'

All of the communication errors I ran into were indicating some sort of permission or connectivity problem, but I think it all revolved around a 32-bit driver on the source and a 64-bit default on the destination. Screwy, but at least I found a way to run SSIS packages directly from TSQL with my 32bit source and 64bit destination.

Linky at 2007-10-2 >

SQL Server

SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech