Follow

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

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
Have more questions? Submit a request

4 Comments

  • 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
    Thomas Lørup Duun

    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
    Thomas Lørup Duun

    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 

Please sign in to leave a comment.