Initialize SSIS Variable with Package Configuration

Question

In BIDS (Microsoft Business Intelligence Development Studio), how can you initialize a variable via a package configuration? We have a script task in a package which uses user variables. The user variables are hardcoded. We'd like to make the script task configurable. We have a SQL Server package configuration table which stores package configurations.

Would we want to initialize the variable in the script task code or can it be done in the variables table?

What is the syntax if the variable is VariableName and the configuration name for the package configuration is "Configuration Name"?

Also, does it make sense to initialize the variable with the package configuration value or would it be better to use the package configuration directly?

These are the current package configurations:

This is from the Variables window:

This is the code from the script task:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Add your code here
        '
        Dim fileName As String

        fileName = CStr(Dts.Variables("User::TrialBalanceReportDirectory").Value) + _
                CStr(Dts.Variables("User::Slash").Value) + _
                CStr(Dts.Variables("User::TrialBalanceReportFile").Value)

        'MsgBox(fileName)

        Dts.Variables("TrialBalanceReportExists").Value = File.Exists(fileName)
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Thank You!

michaelc35

SebTHU SebTHU · Accepted Answer · 2016-03-30T12:56:33

It's generally clearer to configure the target property directly, rather than using a variable in between.

But since you're referencing the values from within a Script task, I can't see a way that that Script task could directly access the configuration values, rather than accessing them through variables. (There may be a way in code, which I don't know of but would be interested to read if anyone posts it).

The syntax to set a variable value from a configuration is:

\Package.Variables[User::VariableName].Properties[Value]

(obviously, replace VariableName with the name of your variable). This value specifies where the configured values should be "poked" into the package. In my config table (using SSIS2008) it goes in the PackagePath column. The value you want to assign to the variable then goes into the ConfiguredValue column.

Initialize SSIS Variable with Package Configuration

1 Answers