다음을 통해 공유

Use SSIS Variables and Parameters in a Script Task

  • 아티클

In SSIS 2012 there is a new task to set the value of variables: Expression Task. For previous versions of SSIS or for more complex constructions you need the Script Task. Here is an example on how to use variables and parameters in a Script Task. 

First you need to add the variables and/or parameters to the readonly and/or readwrite variables. Edit the Script Task 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:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
 
namespace ST_def94055cb1544e4aa823a304f97288f
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial  class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void  Main()
        {
            // Variables and parameters work the same in a Script Task, but there are two differences:
            // 1) Parameters have a different Scope (the part before ::)
            // 2) Parameters are always read-only
 
            // Some examples:
 
            // Read an SSIS package parameter and sets its value in an SSIS variable
            Dts.Variables["User::MyIntegerVariable"].Value = Dts.Variables["$Package::MyIntegerPackageParam"].Value;
 
            // Read an SSIS package parameter and sets its value in an SSIS variable
            Dts.Variables["User::MyIntegerVariable"].Value = Dts.Variables["$Project::MyIntegerProjectParam"].Value;
 
            // Read an SSIS variable and add 1. Because .Value returns an OBJECT, you first need to convert it to an integer
            Dts.Variables["User::MyIntegerVariable"].Value = Convert.ToInt32(Dts.Variables["User::MyIntegerVariable"].Value) + 1;
 
 
            Dts.TaskResult = (int)ScriptResults.Success;
        }
 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

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.

Parent package variables
A child package can read the variable values of a parent package with a Script Task. But because the names of those parent package variables are unknown for the child package at design time, you can't select those variables names with the select window. So you have to type the exact name (incl. scope) in the readonly variables textbox of the Script Task. Make sure you don't use the same variable names in the parent and child package, otherwise it won't work.

With this simple line of code you can transfer the value of the parent package variable to the child package variable:

// Store parent package variable value in child package variable
Dts.Variables["User::MyChildPackageVariable"].Value = Dts.Variables["User::MyParentPackageVariable"].Value;

For a VB.Net version of the code and more details check this blog post.

See Also