CODEWITHSUNDEEP
sql-serverssisetl
narendra gc
narendra gc

Reputation: 169

SSIS: Conditional Split result to variable

I have condition where I need to feed conditional split result based on condition (will be just one int value) to variable. Can some one help how to do this?

My actual package (Data flow):

XML Source --> Conditional split (based on condition) 2 outputs..one result based on condition (will be just one int value) need to pass it on to variable. How to achieve this?

Upvotes: 3

Views: 3358

Answers (3)

Hadi
Hadi

Reputation: 37368

you have to use a script component to achieve this:

  • Create a script component (Choose it's type as Destination)

enter image description here

  • Double click on script component and Choose your Variable as a ReadWrite Variable. (in my example the variable is named Result

enter image description here

  • Inside the script window write the following code

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    If Not Row.inColumn_IsNull Then
        Variables.Result = Row.inColumn
    End If

End Sub

Side Note: Variable value does not change before dataflowtask execution is finish, to use the new value you have to continue your work in another dataflowtask

Upvotes: 2

RoundFour
RoundFour

Reputation: 347

I know two ways to realize this.

First: Do it with a C# script component, this is the easiest way. Just put a script component behind your conditional split, select your variable as "ReadWrite", then set it like that in the code:

Dts.Variables["yourvariable"].Value = Input0Buffer.Yourcolumn

I don't have access to SSIS right now, so I can't give you the exact code, but this should get you started.

Second: Write to a table and read it back with Execute SQL task. I don't really like this way ;-)

Upvotes: 0

Ferdipux
Ferdipux

Reputation: 5256

Pure SSIS way - consume your dataflow into a Recordset Destination and then iterate through it with ForEach Loop, assigning value to the desired variable.

Upvotes: 3

Related Questions