Saturday, February 25, 2012

Data Mart rollbacks

How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.
Hello Joe,
What about putting the Tasks (Execute Package tasks) in a transaction?
http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> How have folks been managing rollbacks on failures inside SSIS when
> populating data marts?
> For example - we have a seperate package for each dimension table,
> then a master Fact table update. If one of the dimension table
> updates fails - how have you rolled back the previous changes in the
> tables updated prior to the failure - or if the Fact tabel package
> fails - how do you manage rollback in all the dimension tables?
> My first thought was using the Audit table information to determine
> which tables needed rolled back.
>
|||Is this what your team would implement?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
> Hello Joe,
> What about putting the Tasks (Execute Package tasks) in a transaction?
> http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
|||Hello Joe,
Yes. I would be looking to put things inside of transactions. I may logically
split things up but yes transactions would be the way for me
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
[vbcol=seagreen]
> Is this what your team would implement?
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
|||the easy way:
backup the DB, execute the ETLs, restore the DB in case of a problem...
in fact, its a big recommendation to always backup first, so there is no overhead here.
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:D6985AF3-799E-4B96-8A87-7A823C9C1FC2@.microsoft.com...
How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.

No comments:

Post a Comment