SSIS job success but package didn't run

  • Mangesh Raut

    SSC-Addicted

    Points: 448

    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?

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32347

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

  • SQLBuddy123

    One Orange Chip

    Points: 28234

    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: 3672

    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: 3672

    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: 28234

    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

    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: 28234

    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

    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: 28234

    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

    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: 28234

    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

    HI

    Same issue i was facing....

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

    it will be fix !!!

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

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