SCOPE_IDENTITY() for bulk insert in SSIS

Question

In SSIS, I am able to make an insert of rows and retrieve their SCOPE_IDENTITY using OLE DB Command Task which calls stored procedure, but this is not bulk insert, it's slow load. Is it possible to get id of inserted rows using bulk insert in SSIS?

Example: When inserting Customer, first I have to insert record in Person table and then use this FK in Customer table.

UPDATE:

Here's a structure of Person and Customer tables that need to be populated from external source.

One option is to have OriginalId in Person table so that I can use it in lookup when populating Customer table. But that doesn't answer my question about SCOPE_IDENTITY and fast load

Desperate

Answer 1

I would suggest performing your inserts in two separate Data Flow Tasks.

Example:

Data Flow 1 - Insert into Person table

Data Flow 2 - Lookup FK from Person table and then insert into Customer table

Adventurism

In that case I need to have OriginalId in Person table, otherwise I am not able to make a lookup – Desperate

Can you update your question to include the table structure? That would help me to understand what you are looking at. – Adventurism

I've posted a new question which is rephrased a bit: #28606195 – Desperate