SSIS 2008: Replacing Natural Keys With Surrogate Keys

[djphatic]

I am building a package in SSIS 2008 and I need to replace the natural dimension keys in my fact table with the surrogate keys in the dimension tables. Each fact has 7 date dimensions, 6 time dimensions and other dimensions.

Is there a method of updating common dimension fields, i.e. all date or time fields, using one lookup transformation or do I need a lookup transformation for each field?

[John Simon]

You need to use a Lookup transformation for each.

[TheNJDevil]

Yes you need a lookup for each one, but I think his question was more tuned toward performance. Do those 7 date dimension lookup transformations actually load the date dimension 7 times? If so, I believe he is looking for a way to not have it load 7 times.

[John Simon]

Use the Cache Transform as a source in a Data Flow. Then point your lookup to the Cache.

[blynch]

I think you will have to have 7 cache files as I think you can only use a cache file once per package.

[John Simon]

Nope.
I just tested it and loaded two tables with the same cache.

[blynch]

Within the same package? I want that to work for me. Will have to look into that, thanks.

[Sponsored content]