Using Windows system variables in a SSIS Package (Visual Studio)

Question

I've created a Windows system variable called DATA_PATH which references to a path.

I'm trying to use it in a Flat File Connection Manager in Visual Studio like this: %DATA_PATH% or @[DATA_PATH], but it is not working.

Answer 1

Correct, you cannot make up syntax and expect it to work.

There are 4 approaches I can come up with to make use of a Windows environment variable in an SSIS package.

1. Use command line arguments
2. Use Configuration
3. Use a Script Task
4. Use Execute Process Task

My sample package looks like the following + a Project Parameter named ScalaHome data type of string and initialized to the same "NOT DEFINED"

Parameter/Variable via invocation

At run-time, you can specify the value of variables. For example, when I run this command

dtexec /file ./Order.dtsx /Set \Package.Variables[User::Counter].Properties[Value];3

It will run the Order package and initialize the value of my variable User::Counter to 3.

Extending that pattern, you can run your package and use your environment variable value to set a package value.

dtexec /file ./Order.dtsx /Set \Package.Variables[User::LocalPath].Properties[Value];%DATA_PATH%

If you're using parameters, the command would be something like

dtexec /package Order.dtsx /project ./MyProj.ispac /PAR  "$Package::LocalPath";"%DATA_PATH%"

EXCEPT, that won't work because

The Parameter option can only be specified with the ISServer option.

So instead, even though there is a /parameter option, you need to use the /SET route. In this example, I'm going to reference my existing SCALA_HOME environment variable

Before

dtexec /proj C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\bin\Development\SO_Trash.ispac /pack SO_74786096.dtsx /rep eiw

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3456.2 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  8:54:58 AM
Info: 2022-12-13 08:52:58.86
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: $Project::ScalaHome:->NOT DEFINED
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  8:52:58 AM
Finished: 8:52:58 AM
Elapsed:  0.422 seconds

After

dtexec /proj C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\bin\Development\SO_Trash.ispac /pack SO_74786096.dtsx /rep eiw /SET \Package.Variables[$Project::ScalaHome];"%SCALA_HOME%"

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3456.2 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  8:53:14 AM
Info: 2022-12-13 08:53:15.15
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: $Project::ScalaHome:->C:\Program Files (x86)\scala
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  8:53:14 AM
Finished: 8:53:15 AM
Elapsed:  0.359 seconds

The development experience is a bit fiddly because even though you can specify CmdLineArguments, adding the same /SET \Package.Variables[$Project::ScalaHome];"%SCALA_HOME%" in there had no effect

Configurations

We're gonna go old-school here as this is no longer a favored method for handling configurations with the advent of the SSIS Catalog. But it still exists, it's just harder to access in the Project Deployment Model. Right-click on a package and select Property. Find the Configurations collection and click the ellipses ...

Check the "Enable Package Configurations" box Click Add...

In the resulting wizard, pick your Configuration Type and find the Environment Variable

In the Next window, you need to set the target property, aka push that value into a local variable. Here I'm using User::ConfigValue and importantly I am expanding the Properties tree and selecting the Value

Give it a name

Ok and Close.

Now when you run it, you'll see 2 information messages. The first that it's trying to use config and the second will be our Script Task spitting out values.

Info: 2022-12-13 09:14:23.72
   Code: 0x40016038
   Source: SO_74786096
   Description: The package is attempting to configure from the environment variable "SCALA_HOME".
End Info
Info: 2022-12-13 09:14:23.84
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: User::ConfigValue:->C:\Program Files (x86)\scala
End Info

Script Task

Add a read-write variable to the mix.

    // Lazy approach and beware the lack of checking
    public void Main()
    {
        var value = System.Environment.GetEnvironmentVariable("SCALA_HOME");
        Dts.Variables[0].Value = value;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Reference answer for C# and accessing environment variables https://stackoverflow.com/a/185214/181965

Execute Process Task

Pop the value of the environment variable into an SSIS variable. The command I'll use is cmd.exe as the process. Arguments are /C echo %SCALA_HOME% Note that I'm routing the StandardOutput to my variable

Putting it all together

C:\Users\bfellows>dtexec /proj C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\bin\Development\SO_Trash.ispac /pack SO_74786096.dtsx /rep eiw /SET \Package.Variables[$Project::ScalaHome];"%SCALA_HOME%"
Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3456.2 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  9:25:32 AM
Info: 2022-12-13 09:25:32.27
   Code: 0x40016038
   Source: SO_74786096
   Description: The package is attempting to configure from the environment variable "SCALA_HOME".
End Info
Info: 2022-12-13 09:25:32.48
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: User::ConfigValue:->C:\Program Files (x86)\scala
End Info
Info: 2022-12-13 09:25:32.48
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: User::EPTValue:->C:\Program Files (x86)\scala
End Info
Info: 2022-12-13 09:25:32.48
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: $Project::ScalaHome:->C:\Program Files (x86)\scala
End Info
Info: 2022-12-13 09:25:32.48
   Code: 0x00000000
   Source: SCR Echo Back SCR Echo Back
   Description: User::SCRValue:->C:\Program Files (x86)\scala
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  9:25:32 AM
Finished: 9:25:32 AM
Elapsed:  0.469 seconds

Echo back script

My all purpose tester script. Pick the variables you want to have written to the Output window as ReadOnly variables.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_710ef65337734ee281706ce24a9162f1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            foreach (Variable item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}:->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Closing thoughts

The "right" answer is super dependent on you and your organization.

If you were engaging me as a consultant, I'd advise against trying to make use of the environment variable. I'd advise you to create a Project level parameter so that all packages can reference the same value. Then when deployed the SSIS catalog and promoted through the environments, I would use the Configuration section of the SSISDB to set the current value for your data path.

If you choose to go against the advice, the Script Task is the cleanest and most native approach but many organizations have silly commandments of Thou Shalt Not Use Script Task/Component in SSIS packages.

Old blog articles I referenced while assembling this answer

• https://billfellows.blogspot.com/2008/09/ssis-configuration-precedence.html
• https://billfellows.blogspot.com/2016/04/biml-script-task-test-for-echo.html