We have a new community! Please visit support.timextender.com
Follow

How to make a scheduled execution fail if an external SSIS package fails

Author: Thomas Hjermitslev Last updated:
integration_services ssis external_executables

Note:  This is an old article, citing an issue with legacy systems or providers. Some troubleshooting tips may still be applicable to newer versions.  However, this article has not been tested with newer releases after 2019.

Introduction

In some cases, it can be useful to use a custom SSIS package as external executable. It could, for instance, take text files from an external source and transform them into a SQL source for TX to use.

Since TX does not know about this arrangement, an execution will complete without errors even if the custom SSIS package failed. This will often result in missing or incomplete data.

There is no direct way to tell TX that the SSIS package failed. However, you can use a "communications" table to inform TX that it should stop executing the project.

Step by step

  1. In the database, create a SQL table that the SSIS Package can write to and TX can read from. Let us call the table SSISStatus and just add a single field, "SSISStatus", with a BigInt data type.
  2. Set your custom SSIS package to write to the table if it fails.
  3. In TX, use the SQL table as data source in the project. Move the table to the top of the staging database to ensure that it will be the first table to get executed.
  4. Add a validation rule on the field SSISStatus. Set the operator to "Equal" and the value to "0".
  5. Add a checkpoint on the table on the staging database. On the table level, set Value type to "number of rows" and Max. Value to "0". The checkpoint will force the project to fail if the number of rows that ends up in the SSISStatus table is greater than 0 and will stop further execution of the project.

    2014-04-11_10h50_17.png

This article applies to: timeXtender 4.5, tX2012, TX2014. Last updated: 2014-04-10.

Was this article helpful?
0 out of 0 found this helpful

5 Comments

Date Votes
  • 0
    Avatar
    Kurt Denolf

    Seems an interesting feature !  Thinking further about this   ...would it not be safer that when then the SSIS package SUCCESSFULLY finishes that it writes a record in this table ?  this would avoid that when the package fails and the error capturing is not well designed that no record is written in this table anyway and so that Tx assumes that it ended without errors...

    So I think it would be safer to let the package insert a record when ended successfully ! But then we do not seem to be able to use this Checkpoint functionality ?  as it has only the option for checking MAX nr of records ...would it be an option for future releases of TX to have also a MIN option ?

    Or do you see other options ?   

  • 0
    Avatar
    Permanently deleted user

    You could probably have a script action that would perform a RETURN 0 if a table does not contain records. Feel free to do some experiments and let us know if you can provide a better solution. 

  • 0
    Avatar
    Kurt Denolf

    I do not completely understand what you mean with "RETURN 0" ? could you provide some more info if possible.

  • 0
    Avatar
    Permanently deleted user

    Hi Kurt, 

    Take a look at this article, where the RETURN command is explained: http://technet.microsoft.com/en-US/LIBRary/ms174998(v=sql.110).aspx 

  • 0
    Avatar
    Femke Kooij

    Hi, instead of failing complete execution, would it be possible to just send an email with a warning message?

    I have an external SSIS package that might fail , hence data is not refreshed. My Timextender project is loading this data and using in a small part of the project. So I don't want to fail the complete execution, I just want a mail to be sent stating that the external SSIS package is not updated and hence needs to be looked at.

    I know I can set a field validation with a warning that shows in the internal report window, but as this won't happen very often, this is not checked on a daily basis'. hence the need for a mail.

     

    Edited by Femke Kooij
Please sign in to leave a comment.