Auto Incrementing a dimension primary key in SSIS

While working with SSIS to generate star schemas, a common problem I faced was populating a dimension table from another table that did not contain any ids for the items being inserted. If you would try to insert the dimension data without any keys, an error would be thrown since the id field cannot be null.

An easy solution is to have a script populate the id column.

In this example we take a table that does not have any ids related to the dimension that we want to populate. First we aggregate the items that we want inserted into the dimension.