there is error in sql server agent for in excute package

[mohamedmaoodah]

my package is to refresh dimension tables when i excute it directly from the designer it execute correctly
even i execute it from Execute package utility it also execute correctly but when i use it from sql server agent it failed and show this error message :

Error: 2010-01-13 10:34:59.07 Code: 0xC0202009 Source: warhouse Connection manager "dw.mm2 2" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: 2010-01-13 10:34:59.09 Code: 0xC020801C Source: refresh the dimention table insert dimension table ora [4096] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "dw.mm2 2" 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: 2010-01-13 10:34:59.09 Code: 0xC0047017 Source: refresh the dimention table SSIS.Pipeline Description: component "insert dimension table ora" (4096) failed validation and returned error code 0xC020801C. End Error Error: 2010-01-13 10:34:59.09 Code: 0xC004700C Source: refresh the dimention table SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2010-01-13 10:34:59.09 Code: 0xC0024107 Source: refresh the dimention table Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:34:55 AM Finished: 10:34:59 AM Elapsed: 3.281 seconds. The package execution failed. The step failed.

if you can please help me for solve this error

notes to understand the error correctly :
1- dw.mm2 2 : is oldb connection for oracle
2- refresh the dimention table : is data flow task
3- "insert dimension table ora" is oldb destinaton to oracle database

Submitted via EggHeadCafe - Software Developer Portal of Choice
MDI Inter-Form Communication with Events
http://www.eggheadcafe.com/tutorials/aspnet/0a957f0d-585a-4a3c-935f-20ce4f44f644/mdi-interform-communicat.aspx

[Todd C]

By default, SSIS packages are saved with password info encrypted with the
User Key. When you ran it via BIDS or DTEXEC utility, your User Key was on
hand to DEcrypt said password.
When you ran it via SQL Agent, it runs under another account (another User
Key) so cannot decrypt the Oracle password.

Couple of possible solutions:
*Create a Proxy account in SQL Agent to run SSIS packages as you (not
recommended because if your password changes, the Proxy will fail)
* Use EncryptSensitiveWithPassword as the Packages ProtectionLevel property
*Use a Configuration in the SSIS design so that the password is saved
external to the package and is retrieved at runtime.

Check out my blog on the subject(s): http://toddchitt.wordpress.com/

Reply back here or at the blog site if you need additional help.
--
Todd C
MCTS SQL Server 2005

"mohamed maoodah" wrote:

> .
>