January 24, 2011 at 8:29 am
Hi,
Is it possible to achieve the rollback of the data which is modified in the database during a package run along with redirecting the error records from a OLEDB Destination source by using some combination of Isolation Level and Transaction Option properties in SSIS package? Currently, If rollback is happening, I cannot able to redirect the error rows & I am not able to achieve the rollback if I am redirecting the error rows.
Please provide suggestions..
Thank you..
January 25, 2011 at 6:46 am
At first glance, I don't think it is possible.
Isn't that the whole point of rolling back transactions? Rolling back to a previous state as if nothing has happened?
If nothing has happened, there are no error rows...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2011 at 6:59 am
If there are 80 records that got inserted out of 100 which are supposed to be inserted into a database, and 81st record got error, then I am re-directing that error 1 record into a flat file. But now, I need to roll back any updates or inserts that have happened due to 80 records that were already processed. Is that possible through any settings in package?
January 26, 2011 at 3:46 am
Raja Suman (1/25/2011)
If there are 80 records that got inserted out of 100 which are supposed to be inserted into a database, and 81st record got error, then I am re-directing that error 1 record into a flat file. But now, I need to roll back any updates or inserts that have happened due to 80 records that were already processed. Is that possible through any settings in package?
It is not possible with settings, I believe.
You could however write the data to a staging table first, check if there are errors and then write the data to the final destination table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply