SSIS: using multicast to enter data in 2 RELATED directions

Question

I am new to SSIS. I have data coming from one source. I need to enter this data into several tables (these tables are connected by foreign key relationships). I use multicast to enter data at multiple destinations.

My question is ...

How to get the record identifier in one destination and use this name for the foreign key column for the second destination?

Here is an example of what I'm looking for. The Employee table has a foreign key in the Address table. But the source includes all this information. Entering data in 2 different places is easy. But I need the identity from the Address table when I enter the information in the Employee table. How to get this identifier?

Source (Excel file)

Name Street State etc... ---- ------ ----- Jim 12345 Plain St. CA Bob 54321 Main St. CA etc. 

Destination

  Address Employee ------- ------- PK AddressId PK EmployeeId Street FK AddressId State Name etc... etc... 

Answer 1

The short answer is that because of this, SSIS was not created for this. After you put the data in the table, the target components do not allow sending the output stream.

You can fake this behavior with OLE DB Command , but your performance will be less good, as it produces a singleton insert statement for every row that flows through the data stream. As a rule, the engine tries to perform N units of work and perform massive, established data operations to obtain greater throughput. You can also use Script Component to accomplish this. The same performance limitation will continue to apply.

It is best to place your data in a staging table, and then use the Execute SQL Task after Data flow and use OUTPUT in the INSERT operation to commit these identifiers and then fix them in another table.

Answer 2

Another approach I would pose with a task like yours is to artificially create an identifier field for the parent table. The idea here is to know the identifier in front so that you can assign foreign key values.

Then, instead of using multicast, load the data sequentially: parent and then child. For the parent table, check the Keep Identity property (OLEDB assignment).

Answer 3

Placement table

 CREATE TABLE [dbo].[Stage]( [Name] [varchar](50) NULL, [Street] [varchar](50) NULL, [State] [varchar](50) NULL, [pkAddressID] [int] NULL ) ON [PRIMARY] GO 

Address table

 CREATE TABLE [dbo].[Address]( [pkAddressID] [int] IDENTITY(1,1) NOT NULL, [street] [varchar](50) NULL, [state] [varchar](50) NULL ) ON [PRIMARY] 

- Employee table

 CREATE TABLE [dbo].[Employee]( [pkEmployeeID] [int] IDENTITY(1,1) NOT NULL, [fkAddressID] [int] NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO 

--- DFT - Loading data into a Stage table

--- Executing SQL task 1 --- Filling the address table

 Merge [dbo].[Address] as target using ( select distinct [Street], [State] from [dbo].[Stage] ) as source on source.[Street] = target.[Street] and source.[State] = target.[State] when not matched then insert ([Street], [State]) values (source.[Street], source.[State]) ; 

--- Executing SQL task 2 --- Populating the Stage table // column pkAddressID

 Merge [dbo].[Stage] as target using ( select [pkAddressID],[Street], [State] from [1Address] ) as source on source.[Street] = target.[Street] and source.[State] = target.[State] when matched then update set target.[pkAddressID] = source.[pkAddressID] ; 

--- Execution of the SQL task 3 --- Filling the employee table

 Merge [dbo].[Employee] as target using ( select [pkAddressID], [Name] from [dbo].[1Stage] ) as source on source.[pkAddressID] = target.[fkAddressID] and source.[Name] = target.[Name] when not matched then insert ([fkAddressID], [Name]) values (source.[pkAddressID], source.[Name]) ; 

Answer 4

Another way to overcome the error: The INSERT statement conflicted with the FOREIGN KEY constraint ... when pasting into two related tables with Multicast, clear the Check constraints option for dependent OLE DB Destination :