SQL Server agent job failed. The package executed successfully

2
votes

Job runs from SQL Server 2008 64 bit

Job step kicks off a SSIS package which runs successfully in BIDS. SSIS package copies data from an Oracle server to a database in SQL Server

The login for the connection manager for the Oracle database is different than the login used to create the SQL Agent job. I doubt this is the issue because I have another job which does the exact same thing with same parameters as this job and SSIS package the only difference is the Job that works connects to a different Oracle database.

The SSIS package is copying over the data nightly, record counts in source (Oracle) and destination (SQL Server) match but the error I get when I view the history is:

X - The job failed. The Job was invoked by.....

Selected row details:
Date 5/31/2013 12:00:00 AM
Log Job History (DailyPull)

Step ID 0
Server SQL00
Job Name DailyPull
Step Name (Job outcome)
Duration 00:34:28
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by Schedule 20 (DailyPullSchedule). The last step to run was step 1 (DailyPull).

1
sql-server-2008jobssql-agent-job
Check the "on success" action for the job step. It is possible to have a successful job report an error, even on success. – Gordon Linoff
I had a similar issue where a job executed a stored procedure successfully but then it reported failure. This shows how brain-dead ssis is. – costa

1 Answers

0
votes

Here is what worked for me:

The problem happened when we changed the account other than who created it and used a proxy for the new account.

  1. copy the package to another folder
  2. changed the Name for the 'CreatorComputerName'
  3. changed the 'CreatorName' to the new account (our new account has a sys admin right)
  4. save and reload the package in the job.

Success!