SSIS Package Design - Ideas

Greetings SSIS friends,

I am in the process of designing a package for populating a Dimension table for my new data warehouse. I would like to discuss with you my proposed solution in the hope that some of you will be able to advise me on any mistakes that I have made so far.

I have 3 source tables which are used to populate my Dimension table (I will restrict each source table to a few columns to keep this simple).

The source tables are :

Meetings (MeetID, NameID, Meeting_Start_Date, Meeting_End_Date, Meeting_Time_Stamp)

Events (EventID, MeetID, Event_Start_Date, Event_End_Date, Event_Time_Stamp)

Names (NameID, Long_Name, Short_Name, Names_Time_Stamp)

My Dimension table structure is very basic as follows :

Product_DIM (EventID, MeetID, Meeting_Long_Name, Event_Long_Name..... Meeting_Time_Stamp, Event_Time_Stamp)

Please note that I haven't included the start and end dates to keep this simple, but in my real solution, the Dimension table has many more columns.

The Meetings and Events tables contains hundreds of thousands of records so when my package is run, I should only retrieve information that has changed since the last time my Dimension table was populated. This is why I store the time stamps in my Dimension table.

So when my package runs, I get max(Meeting_Time_Stamp) and max(Event_Time_Stamp) from my Dimension table then retrieve all records from the source table where their timestamps are GREATER than the max values.

The problem is this :

If in the source system, an event is changed, the time stamp associated with that record will change value but the meeting record associated with that meeting will not have its time stamp changed.

How do I ensure that when my package is run, I retrieve all meeting and events information even if one hasn't changed?

 

Should I build a table made of IDs?! And if I do need to build a table made up of IDs, should I store this as a permanent table in my database or is there an SSIS data structure I can use to hold this information?

I hope this makes sense!