SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources

View previous topic View next topic Go down

SSIS Frameworks. Automatic Rollbacks, and Poorly Designed OLTP Sources

Post  DavidStein on 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

View user profile

Back to top Go down

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

Post  ngalemmo on 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?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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