Search code examples
ssisdataflow

SSIS Data Flow - sequential insert with required foreign key values

Is it possible to insert to a initial table then using the inserted ID to be used in inserting to the main table that has a foreign key constraint between the columns in one data flow?

I am new to integration services and don't know the capabilities

Scenario:

TABLE A - ID - DESC

TABLE B - ID - A_ID - DESC

This is where A_ID is the foreign key (if its not obvious)

Can i create a single data flow with this kind of sequential flow of events or do I need to create another data flow for insertion of TABLE B after I inserted in table A.

Thanks

Solution

  • There are no precedence constraints or containers in a data flow, so if you put both operations into one flow they execute in parallel and that means you can't make sure they will complete in the necessary order. So just use two data flows and then you can control the order in the control flow.