CODEWITHSUNDEEP
sqlsql-serverssisetlidentity
Calvin Ellington
Calvin Ellington

Reputation: 723

SSIS Derived Column Primary Key Creation

I am attempting to create an archive table for a table that I've built in SQL Server. Using an SSIS package I'd like to record each change to the table and add this to the archive table. If successful I should be able to both query the archive and build the table that existed at a specific date range, and be able to query the table to see a list of changes in a date range. In the same SSIS package that updates my report table from the data sources I am using, I have a derived column task which will insert a BOOLEAN value, a transaction date, and I'd like to add a unique integer id for each transaction. There is not a clear way to do this and I'm wondering if someone could inform me?

For reference my model for this project is:

Source DATA --> SSIS --> Report Table --> Archival Table

I cannot use CDC as that is not suported in the standard version of SQL server. Here is what my derived column task looks like now. I am wondering what kind of expression I could use to get a unique primary key value as the Tx_ID

enter image description here

Upvotes: 1

Views: 4128

Answers (1)

Hadi
Hadi

Reputation: 37313

1st Method - Add an Identity column

This is the most simple and recommended method, if there is no need to use the identity value within the package, just add an Identity column to the destination table

ALTER TABLE [Archival Table]
ADD Tx_ID INT IDENTITY(1,1)

2nd method - using a Script Component

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

    SELECT MAX(Tx_ID) FROM [Archival 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: NewTxID)

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

    End Sub  

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

        CurrentID += 1

        Row.NewTxID = CurrentID


    End Sub 

End Class

  6. In the OLEDB Destination Map the NewTxID column to the destination identity column

References

Upvotes: 1

Related Questions