Share via

Use SSIS Variables and Parameters in a Script Component

  • Article

Here is an example on how to use variables and parameters in a Script Component.

First you need to add the variables and/or parameters to the readonly and/or readwrite variables. Edit the Script Component and in the Script Pane you will find two textboxes: ReadOnlyVariables and ReadWriteVariables. They are for variables and parameters.

Note: parameters are always readonly. Variables can be readonly or readwrite.

Now you can use them in the script. There is one big difference with the Script Task: reading and/or writing variables and parameters can only be done in the PreExecute and PostExecute method.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class  ScriptMain : UserComponent
{
    // .Net variables to store values from parameters and variables.
    // Declare these outside the methods so that you can use them
    // in all methods.
    int myCounter = 0;
    int myTotalSales = 0;
 
    public override  void PreExecute()
    {
 
        base.PreExecute();
        // The method that executes first is a good place to read variables and parameters.
 
        // Because you can't read variables in other methods like Input0_ProcessInputRow,
        // you must use .Net variables to store the variable or parameter value.
        // Note: in contrast to the Script Task, you don't use scopes in the variable and
        // parameters names:
        myCounter = this.Variables.MyIntegerVariable;
 
        // example with parameter: There are no differences between code for parameters
        // and variables
        myTotalSales = this.Variables.MyIntegerPackageParam;
    }
 
    public override  void PostExecute()
    {
        base.PostExecute();
        // The method that executes last is a good place to write variables, but there is
        // one big drawback/issue: The variable is filled when the Data Flow Task is finished.
        // So you can't use the variable value in the same Data Flow Task, because it will
        // still have the old value.
        this.Variables.MyIntegerVariable = myCounter;
    }
 
    public override  void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Count sales from all records and store result in .Net variable
        myTotalSales = myTotalSales + Row.Sale;
 
        // Count records and store result in .Net variable
        myCounter++;
 
        // or count records and store result in a (readwrite) column
        Row.RowNumber = myCounter;
        Row.CumulativeSales = myTotalSales;
    }
}

For a VB.NET version of the code, check out this blog post. It also shows a second option with the VariableDispenser, but that isn't the preferred method.

See Also