2

I am new on SSIS and have the following question/problem.

Can a variable be updated in Data flow using SQL? For instance, I need to assign the numeric value of maximum ID available in the Database (and when the etl is running the max value changes). So I need to update the variable with every row inserted.

Is this possible? Or any other way?

I have tried using variable, but it's value isn't changing in data flow. I also got few answers related to OLE DB Command Script in Data flow, but I am unable to understand it.

Bilal
  • 55
  • 1
  • 8
  • 1
    Assume 7 rows of data. Row 1 is processed, gets an id 100 (queries database for max ID and adds 1) and is stored. Row 2 is processed, it gets an id of101 and stored, etc. Is this what you are attempting to do? – billinkc Jul 21 '19 at 18:10
  • Yes @billinkc. Exactly this. – Bilal Jul 21 '19 at 18:40
  • see if this [Q/A](https://stackoverflow.com/questions/54402110/how-to-use-a-variable-value-created-in-an-execute-sql-task-in-precedence-constra/57128742#57128742) is helpful. – Weihui Guo Jul 21 '19 at 21:00

1 Answers1

0

You have to do this in two steps:

1 - Read Max ID from SQL and store it into a variable

First of all you have to use an Execute SQL Task to read the Max ID from SQL Table into a variable. You can refer to the following article for a step by step guide:

2 - Increment the ID for each Row using a Script Component

After storing Max ID in a variable (example: @[User::MaxID]), you have to use a Script Component inside a Data Flow Task to increment it for each row.

Add a Script Component Transfromation inside the Data Flow Task, select User::MaxID as ReadOnly variable, add an output column (NewID) and use a similar Script (example in VB.NET)

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

        Base.PreExecute()

        CurrentID = Dts.Variables.Item("User::MaxID").Value

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

        CurrentID += 1

        Row.NewID = CurrentID


    End Sub 

End Class

Similar posts

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hello @hadi. Thank you for the solution. I think your solution might fail on few "specific" conditions (for my problem). I found two solution, both of them with OLE DB Command. I will post them in detail. Nevertheless, thank you for the response, it routed me to correct direction! – Bilal Jul 26 '19 at 07:15
  • 1
    @Bilal but be aware that OLE DB command will decrease performance since it executes an sql command on each row – Hadi Jul 26 '19 at 07:23