Click here to Skip to main content
13,139,569 members (54,722 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

5.9K views
1 bookmarked
Posted 26 Sep 2014

All About SSIS Variables Part 3 Variable Assignment

, 26 Sep 2014
Rate this:
Please Sign up or sign in to vote.
This post discusses about variable value assignment in SSIS.

The sample package outlined in this post can be downloaded here.

While my last post discussed SSIS object variables, this post will look at variable value assignment. There are several ways that a variable can be assigned a value within an SSIS package:

Default Value Assignment

Once a variable is declared, scoped, and a data type defined, a default value can be assigned within the Value field. This is the value that the variable will take once it falls in scope. This will remain the variable value until a task changes that value:

Expression

An SSIS expression can be used to assign a value to a variable which, like the Value property, is the value that will be assigned once the variable falls into scope. In SSIS 2005-2008R2, the variable expression value had a limitation to 4,000 characters. This could be overcome by using an expression to concatenate several together. I outlined this technique here.

Expression Task

An expression task, first introduced in SSIS 2012, provides the ability to use an SSIS expression to assign a value to a variable once the task is executed:

Script Task

A script task provides two separate means for variable value assignment.

ReadOnly/ReadWrite

The first, and probably the easiest, is to list the variables for ReadOnly or ReadWrite access within the components configuration pane:

With the variable(s) being entered within the component code is written that we don’t see that handles the locking and unlocking of the variable(s) for both read and write. The system generated code allows us to interact directly with the variables without having to first lock the variables. For example, the below VB.NET code will use the MessageBox Show method to display the value of the variable “Today” that was enabled for ReadWrite access, then change the value, and once again display the new value in a message box:

MessageBox.Show(Dts.Variables(0).Value.ToString)
Dts.Variables(0).Value = Now.AddDays(-1)
MessageBox.Show("The new date is " + Dts.Variables(0).Value.ToString)

This provides quick and easy access to the variable for read and write, but does limit when the variable is locked and unlocked based on the system generated code. For more granular control over variable locking, you can utilize the VariableDispenser within your code.

VariableDispenser

Utilizing the VariableDispenser method does require more code, but again provides complete control over the locking and unlocking of variables. DO NOT include the variable within the script configuration ReadOnly or ReadWrite property when using this method or an error will arise when you attempt to programmatically lock or unlock the variables since the components system generated code handles this. The below VB.NET code demonstrates using the VariableDispenser to work with variable directly within a script task:

Public Sub Main()
Dim vars As Variables = Nothing
Dim myVar As Variable
Dts.VariableDispenser.LockForRead("User::Today")
Dts.VariableDispenser.GetVariables(vars)
For Each myVar In vars
MessageBox.Show("Variable value " + myVar.Value.ToString + " and name is " + myVar.Name)
Next

Dts.VariableDispenser.LockForWrite("User::Today")
       For Each myVar In vars
myVar.Value = Now
Next
For Each myVar In vars
MessageBox.Show("Variable value " + myVar.Value.ToString + " and name is " + myVar.Name)
Next
vars.Unlock()
Dts.TaskResult = ScriptResults.Success
End Sub

The above code uses a foreach loop to iterate through all locked variables, which in this case is only the User::Today variable. To access the variable properties and methods directly, the vars(i) can be called, where “i” is the zero based indexed value of the variable. For example, to re-assign the today variables value, the following code could be used to assign the current:

vars(0).Value = Now

Execute SQL Task

The execute SQL task can also be used to assign a variable a value in two different ways.

Output Parameter

Variable assignment can be done by using a query that assigns the result(s) to a placeholder of a “?“. For example, the below query would take the result of SELECT GETDATE() and assign it to the output parameter that is being held with the “?

SELECT ? = GETDATE()

 

Using the query with the placeholder alone does not complete the assignment and still requires the mapping of the variable to the output parameter which is done on the Parameter Mapping page. On the parameter mapping, you select the variable, in this case User::Today, the direction, which is output, and the ParameterName, which will be the zero based index of the value, in this case 0. The question often comes up as to whether a named output parameter can be used rather than the indexed value and the truth is that it depends upon the connection manager. This post outlines the different configuration methods available for output parameters.  

Result Set

Another way to use an execute SQL task to assign a value to a variable is by using a result set. This is very similar to an output parameter and only differs in how the query is written, there is no “?” place holder or parameter name, but rather just the result set type and result set mapping. On the execute SQL configuration page result set type must be defined as None, Single row, Full result set, or XML, the type is completely dependant upon the type of result(s) that will be provided from the query:

In the Result Set pane, you again map the zero based index results to the return value(s) of the query. In this case, there is only one query that will return a result so the indexed value is zero.

One benefit that you may immediately notice is that if you have multiple variables requiring assignment, such as the current date and the last date a process ran, you can do this within one single execute SLQ task using output parameters, while it would require 2 execute SQL tasks each with a single row result set. There is the possibility to utilize one execute SQL task using a result set of a full result set or possibly XML this would be overkill in such a situation and more easily accomplished with output parameters.

Data Flow Task

The data flow task can be used for variable assignment, for example, using the Row Count transformation to assign the number of rows that pass between two data flow components, or a complete result set using a Recordset destination. To go back to my previous post that covered the object variable data type, I used an execute SQL task to populate the variable. Rather than an execute SQL task, I can use a data flow task that gets the FirstName and LastName columns from the Adventureworks2012.Person.Person table and sends the result set to a Recordset destination mapping my object variable, in this case the User::Names variable:

 

The sample package outlined in this post can be downloaded here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

derekman9707
Database Developer
United States United States
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170915.1 | Last Updated 26 Sep 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid