SSIS Tip – Read from Variables to Rows in Data Flow using Script Task

Reading from variables to augment rows with data from variables isn’t readily apparent in SSIS. A workaround I found was to use a script task right before your OLE DB Destination and access the SSIS variable objects directly.

Script Task Setup

  1. Start with a standard Script Task in your data flow.
  2. Add columns to your output in the ‘Inputs and Outputs’ tab, set the names and output data types appropriately
  3. Access Variables in your ‘main’ script section by using the following code
IDTSVariables100 vars = null; //Gets reference to the SSIS Variables
this.VariableDispenser.LockOneForRead("session", ref vars);//Locks the Variable in quotes, for Read ….use LockOneforWrite to write to variable
string temp = (string)vars[0].Value;//Assigns to value to a string
Row.sessionId = Convert.ToInt32(temp);//converts to Int
vars.Unlock();//Unlocks the var.

Leave a Reply

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