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
- 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.
- Set your custom SSIS package to write to the table if it fails.
- 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.
- Add a validation rule on the field SSISStatus. Set the operator to "Equal" and the value to "0".
- 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.
This article applies to: timeXtender 4.5, tX2012, TX2014. Last updated: 2014-04-10.