How can I use the auto-generated Id of a record I added in a subsequent task?

Question

I am currently adding some records to a table using the OLE DB Destination. Each record has an auto-generated Id field. I want to use this generated Id field as a foreign key in some child records.

I thought I would be able to a data flow line from one OLE DB Destination component to another, but this is not supported.

I would have thought this was a common problem - how do others solve it?

In T-SQL you can get the value of the identity just generated from `SCOPE_IDENTITY()` but that's only if it's on a row by row basis. OLE DB Destination doesn't work row by row. Anyway the way I do this is stage the data into a table and use stored procedures to do it all. What you want probably is possible in SSIS if you use an `OLE DB Command` which does things one row at a time (and is very slow) — Nick.Mc, Jan 19 '18 at 05:36

Hadi · Answer 1 · 2018-01-21T21:42:28.230

Workarounds

(1) Generate identity values using Script component

Before DataFlow Task add an Execute SQL Task that return the MAX(ID) from this table
```
SELECT MAX(ID) FROM MY_TABLE
```
Store the result in a Variable (ex @[User::MaxID]) using a Single Row ResultSet
In the DataFlow Task Add a Script Component, Mark @[User::MaxID] as ReadOnly Variable
Add an Output Column of type DT_I4 (ex: NewID)

In the Script Editor use the following Code (i used Visual Basic language)

Imports System  
Imports System.Data  
Imports System.Math  
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper  
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper  

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _  
<CLSCompliant(False)> _  
Public Class ScriptMain  
    Inherits UserComponent 

    Private CurrentID as Integer  = 0

    Public Overrides Sub PreExecute()  
        MyBase.PreExecute()  

        CurrentID = Me.Variables.NewID

    End Sub  

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

        CurrentID += 1

        Row.NewID = CurrentID


    End Sub 

End Class

In the OLEDB Destination check the Keep identity option, and Map the NewID column to the destination identity column

Then you can use the NewID column before that data is imported to OLEDB Destination, because the identity values are predicted in this workaround. (you can add Multicast component to duplicate the data flow, if you need to perform another operation in parallel)

References

(2) Use staging table

Create a staging table with an identity column
Insert data into staging table
Use the staging table in the Data Flow Task

I wrote my answers in VB because it is easier to understand. But i like writing C# code more than VB. :) — Hadi, Jan 19 '18 at 16:17

score 1 · Accepted Answer · edited Jan 29 '18 at 23:32

1

I ended up using the approach described here:

How to insert scope_identity from parent table into the child table using SSIS

In my case it looked a bit like this:

INSERT INTO dbo.Benefit
 (PeriodId,
  BenefitCode,
  ...)
VALUES (
  ?,
  ?,
  ...);

SELECT ? = SCOPE_IDENTITY()

edited Jan 29 '18 at 23:32

Hadi

36,233
13
65
124

answered Jan 29 '18 at 23:29

Hoppy

720
2
12
24

How can I use the auto-generated Id of a record I added in a subsequent task?

2 Answers2

Workarounds

(1) Generate identity values using Script component

(2) Use staging table

Linked