Help! Is it possible to use Row_Number with Concurrent Inserts?

I appreciate the reply, but that approach still introduces a lot of complications around job sequencing. Do we know if/when Microsoft is planning to add some kind of identity feature?

There are often cases where you need to have your primary keys (PK) generated in order to use those PKs as foreign keys (FK) in dependent tables. The lack of an auto-generated identity means all of your pipelines have to be built in such a way that they insert into the PK table, wait until all other loads complete, and THEN resume with inserts into the dependent tables that need to use the PK as an FK. To make matters worse, that means unrelated jobs have to wait on each other (File Type A's load is held up by File Type B's load).

Typically, any given pipeline would run through steps of a process for a particular ELT job. So the logic would be something like this:

1. Raw data would be staged via Copy activity into DW.
2. "Parent" dimension records would be inserted.
3. "Child" dimension records would be inserted with FK references to the PK for #2.
4. Dimension keys for #2 and #3 would be assigned to staged data.
5. Data with keys would be loaded from stage to final DW tables.

Having to assign PKs at the end for the final table load introduces a huge amount of overhead if you have, say, 15 different data feeds that all insert into the same tables for #2 and #3.