none
Get max value of column RRS feed

  • Question

  • Hi,

    I want to create a ssis package to import data in some tables. The first column of tables is an id, which I have to increase each time I insert a new record. So, I want to get before each insert, the max(id) of column in a variable, increase this variable by one and use it for the next record. How can I do this?

    Thanks in advance,

    CK.
    Wednesday, December 9, 2009 5:07 PM

Answers

  • Hi,

    I want to create a ssis package to import data in some tables. The first column of tables is an id, which I have to increase each time I insert a new record. So, I want to get before each insert, the max(id) of column in a variable, increase this variable by one and use it for the next record. How can I do this?

    Thanks in advance,

    CK.
    I suspect you need surrogate key generation. The materials below are good reference:

    http://www.sqlis.com/post/Generating-Surrogate-Keys.aspx
    http://sqlblog.com/blogs/marco_russo/archive/2005/05/30/surrogate-key-generation-in-ssis.aspx
    http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/
    http://www.cozyroc.com/script/counter-transformation

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Wednesday, December 9, 2009 5:19 PM
  • Take a execute sql task at control flow and capture the max value of the column in a string variable
    Execute SQL Task query will be like:
    SELECT cast(COALESCE( MAX(ID ), 0 ) as varchar(20)) AS MAXID FROM dbo.TABLE and select the result in a string variable (say MAXID)
    Then use a script component and make the above variable as read only. Create a new output column (ID) in script component using Inputs and Outputs-->Output0-->Output columns-->Add Column
    Public Class ScriptMain
        Inherits UserComponent
        'Declare a int variable
        Dim Counter As Integer
        Public Sub New()
            'Initialise the variable
            Counter = 0
        End Sub
    
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            Counter += 1
            Row.ID = Counter
        End Sub
        Public Overrides Sub PreExecute()
            MyBase.PreExecute()
            Counter = Integer.Parse(Variables.MAXID)
        End Sub
    
    Now use this new columd ID inside the data flow

    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, December 9, 2009 5:14 PM

All replies

  • Take a execute sql task at control flow and capture the max value of the column in a string variable
    Execute SQL Task query will be like:
    SELECT cast(COALESCE( MAX(ID ), 0 ) as varchar(20)) AS MAXID FROM dbo.TABLE and select the result in a string variable (say MAXID)
    Then use a script component and make the above variable as read only. Create a new output column (ID) in script component using Inputs and Outputs-->Output0-->Output columns-->Add Column
    Public Class ScriptMain
        Inherits UserComponent
        'Declare a int variable
        Dim Counter As Integer
        Public Sub New()
            'Initialise the variable
            Counter = 0
        End Sub
    
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            Counter += 1
            Row.ID = Counter
        End Sub
        Public Overrides Sub PreExecute()
            MyBase.PreExecute()
            Counter = Integer.Parse(Variables.MAXID)
        End Sub
    
    Now use this new columd ID inside the data flow

    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, December 9, 2009 5:14 PM
  • Hi,

    I want to create a ssis package to import data in some tables. The first column of tables is an id, which I have to increase each time I insert a new record. So, I want to get before each insert, the max(id) of column in a variable, increase this variable by one and use it for the next record. How can I do this?

    Thanks in advance,

    CK.
    I suspect you need surrogate key generation. The materials below are good reference:

    http://www.sqlis.com/post/Generating-Surrogate-Keys.aspx
    http://sqlblog.com/blogs/marco_russo/archive/2005/05/30/surrogate-key-generation-in-ssis.aspx
    http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/
    http://www.cozyroc.com/script/counter-transformation

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Wednesday, December 9, 2009 5:19 PM