How to update variable value in Data Flow ssis?

Question

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.

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
– billinkc, Commented Jul 21, 2019 at 18:10

Hadi · Accepted Answer · 2019-07-22 16:04:36Z

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:

SSIS Basics: Using the Execute SQL Task to Generate Result Sets

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

Add incremental number while inserting records using SSIS

edited Jul 22, 2019 at 16:04

answered Jul 21, 2019 at 19:00

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bilal Over a year ago

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!

Hadi Over a year ago

@Bilal but be aware that OLE DB command will decrease performance since it executes an sql command on each row

Collectives™ on Stack Overflow

How to update variable value in Data Flow ssis?

1 Answer 1

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

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

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

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

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related