Skip to main content

Defining and Using Variables in Biml

Why Variable Scope Matters​

Variables are one of the building blocks of any non-trivial SSIS package. In Biml, a variable is a tag that can be declared on almost any node, and the node where it is declared determines the scope in which the variable is valid. Tasks can only reference variables that are visible in their scope, so the same variable name can carry different values inside a container than at the package level.

Building a Package That Demonstrates Scope​

The example below produces a package with three Execute SQL tasks that insert the value of a variable named 'location' into a logging table. The variable is declared once at the package level and once again inside a sequence container. The first and third inserts run at package scope and write the package level value. The middle insert runs inside the container and writes the container level value.

Tag Notes​

Three tags carry the weight of this pattern:

  • 'Variables' can sit under any tag that supports them. The text of the 'Variable' element is the variable value, and the 'Name' and 'DataType' attributes are required. A task that references a variable resolves to the nearest declaration in scope, which means a container level declaration shadows a package level declaration of the same name.
  • 'Container' requires a 'ConstraintMode' attribute or the designer reports an error. Tasks belonging to the container must be nested inside a 'Tasks' element.
  • 'ExecuteSQL' references variables by 'User' or 'System' prefix plus name (for example 'User.location'). Each parameter on the task needs a 'Name' and 'DataType'.

The Biml​

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="StagingDb" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2019;Integrated Security=SSPI;Initial Catalog=Staging" />
  </Connections>

  <Packages>
    <Package Name="ScopeDemo" ConstraintMode="Linear">

      <Variables>
        <Variable Name="location" DataType="String">package</Variable>
      </Variables>

      <Tasks>

        <ExecuteSQL Name="LogValueOne" ConnectionName="StagingDb">
          <DirectInput>insert into dbo.ScopeLog(value) values(?)</DirectInput>
          <Parameters>
            <Parameter Name="location" DataType="String" VariableName="User.location" />
          </Parameters>
        </ExecuteSQL>

        <Container Name="InnerScope" ConstraintMode="Linear">
          <Variables>
            <Variable Name="location" DataType="String">container</Variable>
          </Variables>
          <Tasks>
            <ExecuteSQL Name="LogValueTwo" ConnectionName="StagingDb">
              <DirectInput>insert into dbo.ScopeLog(value) values(?)</DirectInput>
              <Parameters>
                <Parameter Name="location" DataType="String" VariableName="User.location" />
              </Parameters>
            </ExecuteSQL>
          </Tasks>
        </Container>

        <ExecuteSQL Name="LogValueThree" ConnectionName="StagingDb">
          <DirectInput>insert into dbo.ScopeLog(value) values(?)</DirectInput>
          <Parameters>
            <Parameter Name="location" DataType="String" VariableName="User.location" />
          </Parameters>
        </ExecuteSQL>

      </Tasks>
    </Package>
  </Packages>
</Biml>

What the Run Produces​

When the package executes, the log table receives three rows. The first and third rows hold the value 'package' because those tasks resolve 'User.location' against the package level declaration. The middle row holds the value 'container' because the inner declaration shadows the outer one for any task inside that container. Once execution leaves the container, the inner declaration goes out of scope and later tasks see the package level value again.