SSIS Data Migration/Daily copy: Parent + Child then many Grandchild tables

Question

Hi, I've read this link here, but this guy is doing a one off copy from 1st normal form to 3rd, so that's not what I'm doing: StackOverflowPosting

I am going from 3rd normal form to 3rd normal form, pretty much the same design, on a daily basis, only copying hte new data.

Example Data

Parent

• ParentId
• Created Date

Child

• ChildId
• Created Date
• FK_to_ParentId
• Child Data

GrandChild1

• GrandChild1Id
• FK_to_ChildId
• GrandChild1Data

GrandChild2

• GrandChild2Id
• FK_to_ChildId
• GrandChild2Data

So Far, I have

• A DataFlow that will pull back all the Parents after a certain Date.
• A DataFlow that will pull back all the Child Tables created after a certain date, with a lookup to find the 'new' FK in the new database.
• Now I need to pull back grand child1 and so on, but I don't want to pull them'all' back,
  do a look up, and throw away the ones I already have, I only want to pull
  back the ones I need.

Any Ideas?

Answer 1

In the end, I've just used a inner join using Date and time where available, but when I get the data, I have to do a lookup to find the 'new' FK Id to put in the child & grand child tables. Which means the data for Parent, and Child still needs to contain the 'old' ID, but that is ok. so for a Child table, you end up with something that looks like this:

Data Source -> Lookup using the current FK on the parent table to find the new parent's PK, adding in what makes it unique [replace the FK with the PK you just found] -> data store.