Automic Workload Automation

I am running SSIS Package but the job ends in End Error DTExec: The package execution returned DTSER_FAILURE (1), but the status of the job is ENDED_OK - ended normally. Is there a way to get ENDED_NOT_OK when the job fails?

Hi Judith,

You can use a Filter to check out the job report or file and change the return code. Just create a Filter object with the message you want to read on the job report/file and use the Output scan tab of the job to execute the filter and change the return code.

If you're into Scripting, you can also use the prep_process* (prep_process_report or prep_process_file) on the post process tab of the job and depending on which information you read, use the modify_state statement.

:) Thanks Simone. That worked!

I'm about to get in to triggering SSIS packages.  Can you share an object export of the job you are using to trigger it?  (and the filter would be cool as well)

https://us.v-cdn.net/5019921/uploads/editor/8z/mb6cz0utzki0.png" width="528">
 Willy_Alvarez_2023 I have attached the filter XML. You can create it then put it on the outputs scan of the job as shown in the image. Hope that helps.

Thanks.. but how are you calling the SSIS package? Are you using DTEXEC?  It would be great not having to do all the research, Im starting from 0

Yes, I am using DTEXEC.
dtexec.exe /F "D:\SSISPackages\PackageName.dtsx"

I considered DTEXEC, but I thought it to be limiting.  So I followed the SQL agent approach that I found in this discussion;

https://community.automic.com/discussion/4878/how-to-execute-sql-server-data-tools-ssdt-2012-package-synchronously-using-sql-job

Using this approach I am able to pass dynamic parameters into the SSIS solution, run it synchronously, and when it returns control it queries the SQLServer database to determine the results.  If problems exist, the error messages are returned to UC4 as well (so the developer does not have to go find them in the SSIS environment.)  I also took advantage of SSIS environment variables for secure credential management.

This type of UC4/SSIS interface however also requires that the developers understand how to code to it.  I set this up and got it ready for a major project that has yet to launch, so I don't really have any real world experiences with it yet, other than I've rolled a single application into production as proof-of-concept.