Biml XIV – Variables & Scope


There are probably a few light-bulb moments for SSIS developers that signify an improvement in learning and development style. One of those has to be the use of variables, and, with the use of variables comes the inevitable learning of scope (or the level at which the variable is valid). In Biml, variables are an interesting tag because they can be defined at various places (or levels) within a document and level determines the tasks of the package that the variable is valid in.

New developers should also be aware that variable definition is a lot more user-friendly in recent versions of SSDT because a variables scope is introduced at the package level, then the variable can be redirected to a task should you wish. Prior versions did not accommodate this and the variable scope was locked in the task that was defined in. For example, if you had a sequence container selected and defined a variable, it would only be available in the sequence container (hence its scope was the container).

Overview

To demonstrate variables in biml, we will create a simple package that inserts the value of variable into a table. That variable will be defined at different levels and therefore we can see how the definition impacts value that is used. It may be easier to start with the completed package (as below). Notice that there are 2 variables called location, and one is only applicable in the container (some_task_container).

In this situation, the variables value is inserted into a table so we’d expect to see the first insert use the value “package” (from the variable variables.location) and the second use the value “task_container” (from the variable some_task_container.location ). Finally, when the last insert fires, the value of the location variable returns to the package scope and the value “package” is used. This is exactly what’s shown in the tables output (note the insert time stamps).

The Biml

The full biml definition is shown below. There are a few points to note and some features that have not been called out in other posts. These are listed as they relate to each tag;

Variables

Variables can be defined for any tag or child tag of the package. The node at which they are defined determines their scope.

Naturally, they require a name and datatype. The value is defined as the nodes text.

A task referencing a variable is restricted to the scope of definition. For example, we cannot refer to the package.location variable while the container.location variable exists.

Container

The container task includes a required constraint mode (BIDS will error if it is not present).

Tasks with the container need to be nested with a tasks tag.

Execute SQL

In using a variable for a parameter (as we have done for the queries parameter), we only need to specify its type as either (User or System) and its name (for example User.location).

Parameters must also be specified by Name and DataType.

 

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

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

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

			<Tasks>

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

			<Container Name="some_task_container" ConstraintMode="Linear"  >

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

				<Tasks>
					<ExecuteSQL Name="insert_n2" ConnectionName="dest_stage">
						<DirectInput>insert into dbo.variables(value) values(?)</DirectInput>
						<Parameters>
							<Parameter Name="location" DataType="String" VariableName="User.location" /> 
						</Parameters>
					</ExecuteSQL>				
				</Tasks>

			</Container>

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

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

</Biml>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s