Writing to a Variable in SSIS Script Component

Someone had asked me over the weekend a very interesting question. They are needing to grab the values from a particular column and then input them into a series of variables. 1 column entry = 1 variable. They were wondering how to accomplish this with SSIS. So I thought I would blog a simple solution to this as it explains how you can also control writing to variables as well.
***Disclaimer: I know that this is not ideal….I am just giving an example here

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

'Rows are read in and then the first column is written to a variable
'dependant upon the row number of the row traveling through
Public Class ScriptMain
Inherits UserComponent

'Just a simple variable to hold the counter
Private iCounter As Integer = 1

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim myvars As IDTSVariables90

Select Case iCounter
Case 1
'We lock the variable for writing. 1st parameter is the name of the variable
Me.VariableDispenser.LockOneForWrite("vFirstLoginID", myvars)

'Now we can write the value to the variable
myvars(0).Value = Row.LoginID

'Now we unlock the variables

Case 2

' ....(on and on for how many ever rows you were reading in of information ....)

End Select

iCounter += 1

End Sub

End Class

Hope this helps !


Leave a Reply

Your email address will not be published. Required fields are marked *