Creating foreign key columns for fact table in SSIS

Question

I'm trying to load data into my fact table using SSIS. For this purpose I need to create lookup for each dimensional table to map primary/foreign keys. But the problem is that my source datafile for fact table doesn't contain columns for foreign keys. My destination fact table contains foreign key columns and some other columns. But the source data does contain other columns but not the foreign key columns. How can I create these columns in order to map them to primary key columns of dimensional tables in Lookup?

The question is a little unclear. Are you asking how to add columns to the source/destination of a data flow? Or are you asking how to match up your data when you have no keys in the source? I'm really not sure. — Jacob H
Load the source file into a staging table, and write a SQL query to join the staging table to the dimensions to provide the dimension keys. Then load from that query into your fact table using INSERT ... SELECT or MERGE. — David Browne - Microsoft

Tab Alleman Tab Alleman · Accepted Answer · 2019-04-22T15:39:37

There are lots of ways to do this.

The best way, as David mentioned, is to load your source data to a staging table, and then from there use a SQL procedure to load it to your destination table.

Another way is to use the Derived column transformation to add new columns in the Dataflow task.

Creating foreign key columns for fact table in SSIS

1 Answers