Dear All,
The package failed during the manual deployment and execution when it was executing DW. The stage was executed completely.
I understand that the data is auto commited in SQL Server therefore it will insert and commit the data till one step before at whatever stage it must have failed.
Is there is any possibility to completely rollback the package execution when failed?
Date
Votes
2 comments
-
Jet Reports Historic Posts Hello Franco,
The tables are executed in a specific order and when a table is executed two things happen:
- Data is transferred from the data source into the destination table, typically through a SSIS package
- The data is then cleansed through a SQL stored procedure which brings in lookup values (the red fields) and anything else that has been added to the table in the project.
The SQL transactions are committed at the completion of each step, so if the execution package gets through 45 tables before failing on one then there isn't a way to roll back the data in those 45 tables to what they were previously. In many cases this is fine as it will simply have a more up to date version of the data in those tables. -
Jet Reports Historic Posts In many cases this is fine as it will simply have a more up to date version of the data in those tables.
This is not entirely true, nor is it something you should rely on. I have experienced issues where a package execution can result in incomplete records being written because the transformation step did not complete. This becomes troublesome with incrementally loaded tables that may skip the record on a re-run.
We have found that the best practice is an 'all or nothing' mindset. Upon a failure, I truncate all _V tables that are incrementally loaded and re-run a full execution on the objects. This can be tiresome with a large set of incrementally loaded tables; try asking JetReports for a SQL query that will loop through and truncate all necessary tables.
Please sign in to leave a comment.