Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources

Go down

SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources Empty SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources

Post  DavidStein Fri Feb 04, 2011 11:23 am

I've purchased the excellent book Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution and read it cover to cover a few times.

http://www.wrox.com/WileyCDA/WroxTitle/Microsoft-SQL-Server-2008-Integration-Services-Problem-Design-Solution.productCd-0470525762,descCd-DOWNLOAD.html

I'm trying to implement the updated framework by Rushabh Mehta.

http://sqlpass.eventpoint.com/Speaker/Details/Rushabh_Mehta


His framework is amazing in that it allows automated rollbacks of dimension and fact table loads. However, my database tables do not have datetime columns indicating when they were inserted/updated. It updates in place, hard deletes, etc. Most of it is non-transactional in nature.

Therefore, I don't see any way that this could be accomplished in such a situation. As I see it, if someone realized that the DW received bad data 3 days ago, I would have to do the following:

1. Restore both the OLTP source and the data warehouse database to the exact same time.
2. Restore the OLTP to the next day and run the extraction.
3. Repeat step 2 for each day until current.

Am I missing something here? I don't think there's any other option because my source database is so poorly designed.

DavidStein

Posts : 24
Join date : 2010-04-01

Back to top Go down

SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources Empty Re: SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources

Post  ngalemmo Fri Feb 04, 2011 2:01 pm

You may be trying to overenginneer a problem that won't happen. What do you mean by 'bad data' and why would it take 3 days to know about it?
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum