SSIS job success but package didn't run

Question

Post reply

SSIS job success but package didn't run

Mangesh Raut

SSC-Addicted

Points: 448
More actions
March 14, 2014 at 6:05 am

#283475

I created a SSIS package that imports a file then moves the file to a folder called Archive.
I created a job that runs the package. The pack works fine when I run it from the MSDB folder. When I run the job I get the following success message:
Started: 5:05:35 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 5:05:35 PM Finished: 5:05:37 PM Elapsed: 1.625 seconds. The package executed successfully. The step succeeded.
showing that the job supposedly completed successfully.
I know that the package the not run because the data did not get imported and the file did not move.
Can anyone explain the package may not run but we still get a job completion status of success?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Looks like maybe it did run but it didn't work as expected?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

How are you scheduling the Job ? Also try to enable SSIS logging in the package and run the Job. That way you can better troubleshoot the issues.

Also this could be a permission issue too.

--

SQLBuddy

kl25 Hall of Fame Points: 3752 More actions · Answer 3

Hard to say. If you haven't already done so, consider adding logging to the package. There's a Stairways article about SSIS logging if it's new to you -- see Logging – Level 11 of the Stairway to Integration Services[/url]. Also curious about the settings for 'ForceExecutionResult'.

kl25 Hall of Fame Points: 3752 More actions · Answer 4

sqlbuddy123 (3/14/2014)
Also try to enable SSIS logging in the package and run the Job. That way you can better troubleshoot the issues.

Must have been typing at the same time you were! 😀

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

kl25 (3/14/2014)
sqlbuddy123 (3/14/2014)
Also try to enable SSIS logging in the package and run the Job. That way you can better troubleshoot the issues.
Must have been typing at the same time you were! 😀

True .. 🙂

--

SQLBuddy

Mangesh Raut SSC-Addicted Points: 448 More actions · Answer 6

I have added the logging and no error is coming in the log file. This is the same package I have deployed on the another server (Development) where it is working fine but on QA server it is not. What I did is, I have made change only in the configuration file like (Server name, Database Name, Source and destination file path) while deploying the package on QA server.

I have also deployed this package on development server but on different location and pointing to QA server then only it is not importing the file.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

Mangesh Raut (3/18/2014)
I have added the logging and no error is coming in the log file. This is the same package I have deployed on the another server (Development) where it is working fine but on QA server it is not. What I did is, I have made change only in the configuration file like (Server name, Database Name, Source and destination file path) while deploying the package on QA server.
I have also deployed this package on development server but on different location and pointing to QA server then only it is not importing the file.

Could be a permission issue. Check the Agent Service permissions whether it has access to the file location. Or try to run the package using a SSIS Proxy with your ID as credential.

--

SQLBuddy

Mangesh Raut SSC-Addicted Points: 448 More actions · Answer 8

Thanks for your reply

I have created a job with same package but pointing to Dev Server and running under the user say XYZ this job is running properly and file also getting imported.

Now issue is I have created another job with same package but pointing to QA server and running under the same user XYZ. Here my job executes properly but file not getting imported.

Here I am using same user to run the job so no issue of permission to access the file.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

Mangesh Raut (3/18/2014)
Thanks for your reply
I have created a job with same package but pointing to Dev Server and running under the user say XYZ this job is running properly and file also getting imported.
Now issue is I have created another job with same package but pointing to QA server and running under the same user XYZ. Here my job executes properly but file not getting imported.
Here I am using same user to run the job so no issue of permission to access the file.

Where are you running the job from ? Is it on the Dev Server ?

--

SQLBuddy

Mangesh Raut SSC-Addicted Points: 448 More actions · Answer 10

I am running the job from Dev server. Previously I was running it from QA server but same issue so I have deployed the package and created the job on Dev Server.

FYI :- I am able to run this package (right click -> run package) from MSDB folder where I deployed it.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 11

Mangesh Raut (3/19/2014)
I am running the job from Dev server. Previously I was running it from QA server but same issue so I have deployed the package and created the job on Dev Server.
FYI :- I am able to run this package (right click -> run package) from MSDB folder where I deployed it.

This is because you are running from either server. This is most probably a SQL Server Agent Service account permission issue.

So previously stated, Create a Credential with your ID and SSIS proxy with that credential. And then run the job under that SSIS proxy.

Check this ..

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

--

SQLBuddy

satishsingh Newbie Points: 1 More actions · Answer 12

HI

Same issue i was facing....

To solve it, just log on sql server anent services with administrator...

it will be fix !!!