Search code examples
ssisvisual-studio-2017

What is the meaning of the dollar sign in front of a variable in SSIS?

I am trying to understand an SSIS package that I did not build and am struggling to find a variable. Lets just call the variable:

MyVariable

However, in the definition of MyVariable, there is an expression, which to my understanding (which is likely wrong) this expression can also have variables in it. In my particular expression there is one like so:

@[$Package::AnotherVariable] + "more stuff whatever"

I'm guessing that $Package is some reference to scope, but i can't seem to find the definition for "AnotherVariable" anywhere. Any ideas?

Solution

  • A normal SSIS variable takes the form of

    @[User::Variable]

    The User is a Namespace while Variable is the variable name

    A Namespace is just a name so I can create 3 variables with the "same" name in the same scope and it works because they're different.

    @[User::Variable]
@[StackOverflow::Variable]
@[billinkc::Variable]

    As a consultant, I often made use of the namespace to ensure my pattern would work at any client in existing packages without name conflicts.

    In fact, every SSIS package you work with has variables in two different scopes: User and System. In the Variables menu, the Grid Options will pop up this and you can check Namespace and/or Show system Variables to see what I'm talking about

    enter image description here

    That's the basics of how one would read a fully qualified variable name.

    But whatabout the dollar sign?

    SQL Server 2012 comes along and gives us the fantastic Project Execution Model. One of the problem/challenges we had was how to allow packages to gracefully pass information, one way, to a child/sub package. Thus, Package Parameters were born. They are special, read only Variables that can receive a value when the package starts.

    They take the form of

    @[$Package::PackageParameterHere]

    What's so special about the $? Nothing, it was just Microsoft's way of laying claim to another Namespace. The package parameter namespace is always $Package

    Will it shock you to learn that they employ the same "trick" for Project parameters? No, not it will not. A project parameter is a read only parameter that every package in a project can reference. Say you do a lot of file work---in development, the base path might be "C:\ssisdata\Project" but when you deploy to production, that path needs to become "D:\ssisdata\Project" or "\server\share\ssisdata\Project" Assuming you use expressions to build paths based on the Project parameter, configuring all your packages to use the correct path is a snap. You merely provide a single configuration for that environment and away you go.

    @[$Project::ProjectParameter]

    Again, $Project is the namespace and ProjectParameter is the actual variable name

    Package and Project Parameters