How can I use the auto-generated Id of a record I

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?

Answer 1

Workarounds

(1) Generate identity values using Script component

1. Before DataFlow Task add an Execute SQL Task that return the MAX(ID) from this table

   SELECT MAX(ID) FROM MY_TABLE
   

2. Store the result in a Variable (ex @[User::MaxID]) using a Single Row ResultSet

3. In the DataFlow Task Add a Script Component, Mark @[User::MaxID] as ReadOnly Variable
4. Add an Output Column of type DT_I4 (ex: NewID)

5. 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
   

6. 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

• How to access ssis package variables inside script component
• Using Variables in the Script Component
• Map Result Sets to Variables in an Execute SQL Task
• SSIS Basics: Using the Execute SQL Task to Generate Result Sets

---

(2) Use staging table

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

Answer 2

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()