Pls. help interpret below SQL log file for my failed Data import SQL Job . And pls advise what to do.
ate,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
09/26/2012 10:00:49,Import PSSI-SO Tables,Error,,SQLSERVER,Import PSSI-SO Tables,,,The job failed. The Job was invoked by User sonny. The last step to run was step 1 (Import PSSI-SO Tables).,00:00:01,0,0,,,,0
09/26/2012 10:00:49,Import PSSI-SO Tables,Error,1,SQLSERVER,Import PSSI-SO Tables,Import PSSI-SO Tables,,Executed as user: SQLSERVER\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:00:49 AM Error: 2012-09-26 10:00:50.31 Code: 0xC0202009 Source: Import PSSI-SO Tables Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sonny'.". End Error Error: 2012-09-26 10:00:50.31 Code: 0xC020801C Source: Data Flow Task 1 Destination - sotemp  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" 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: 2012-09-26 10:00:50.31 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Destination - sotemp failed validation and returned error code 0xC020801C. End Error Error: 2012-09-26 10:00:50.31 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-09-26 10:00:50.31 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:00:49 AM Finished: 10:00:50 AM Elapsed: 0.468 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Whatever account (sonny) invoked the SQL job does not have adequate permissions for part of the SSIS.
The job runs in the security context of the executing user. That is to say that if you start it manually then it uses your credentials and if you let it run on schedule then it uses the permissions of your SQLAgent account.
Thanks for your prompt reply, sorry if i was not be able to reply back, I cannot log on to the forum because I forgot my password .
Any way with regards with your analysis, I did tried also using our domain administrator account. but the same problem encountered. what do you think is the best thing to do in order to resolve this issue?