Results for category "Variables"

All About SSIS Variables Part 3 Variable Assignment

sqlsafety 0 Comments

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:



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 execute:


Script Task

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


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:

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

This provide 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.


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
myVar As Variable

For Each myVar In vars
(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name)


       For Each myVar In vars
= Now

For Each myVar In vars
(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name)

Dts.TaskResult = ScriptResults.Success
End Sub

The above code uses a for each 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 “?



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.

All About SSIS Variables Part 2 Objects

sqlsafety 0 Comments

My last post looked at a simplistic example of using an SSIS variable in a row count transformation and then using the variable in a precedence constraint expression to direct the flow of the package.  This post will focus on the unique object variable  data type.  The object data type is the ultimate base class in .NET.  What exactly does that mean?  Object is the root of the type hierarchy, which means that everything, at its core is an object.  MSDN fully documents the object data type here as well as provides sample code here.

One of the more interesting values that can be held within an objects is a full result set, which can then be treated similar to a collection.  One thing that I want to point out is just because you can doesn’t mean you should.  Quite often I will be asked how a result set can be contained in a variable in SSIS and when I ask why I am most often told that it would be used to do complex transformations on the results in the control flow.  HOLD ON!!  That is exactly what the data flow task is for!!  In response to this I am most often told that the transformations are most easily done in a script task.  Once again there is a script component in the data flow that can act as a source, transformation, and/or destination and such complex transformations should be kept in the data flow as a best practice as well as for performance considerations.

If I haven’t scared you away already let me point out several other performance considerations.  First keep the scope of the variable limited to only what is needed, task, container, and only if necessary the package.  The memory required to hold an object will be dictated by the value(s) that are stored in the variable so extra attention is required to limit the exposure to only what is required.  Boxing and Unboxing can also carry performance costs, MSDN documents boxing and unboxing here.

Enough of the gloom and doom, let’s take a look at an object variable and how to work with it.  To properly present the demonstration let’s present the requirements of the sample package.  A result set from an execute SQL task needs to be captured within the object variable that will consist of two columns, FirstName and LastName.  The result set will then be iterated through to execute complex logic, in this case just pass the values to a script task that will present the name in a message box(I know!!  This is just a simple example though).  This package will require three variables,

1. Names                        Object

2. FirstName                    String

3. LastName                    String


With the variables created we can now begin to populate the Names variable with our execute SQL task.  The task will need to set the Result Set property to full result set and the Connection Type will use an OLEDB connection to my localhost default instance connecting to the Adventureworks2012 database.  The query is simplistic enough:

SELECT FirstName,
FROM Person.Person

We now need to define the Result Set settings in the task to specify that the results will be captured within our Names object variable.  From within the Result Set tab you must specify the zero based index value of the results to be stored in the Name property, since our query only returns a single result set this will be set to 0, and then the Variable Name of the SSIS variable that will be used to hold the result set, in this case Names.


Our package will now populate the Names variable with the results of our query and we now have several methods that we can work with this variable.

ForEach Container

The first way to work with an object is to use a For Each container and set the Enumerator to a Foreach ADO Enumerator and define the ADO object source variable to our Names variable:


Now keep in mind that the Names is an object, very much like an array or collection, so we need to take both columns, FirstName and LastName, and place them in their own variables.  This is done on the Variable Mapping page by again mapping the zero based index value of the ordinal position of the columns to the appropriate variables:


**I intentionally placed the LastName and FirstName out of order to demonstrate how the variable mapping is done based on the zero based index and not by the order in which the variables are mapped to the columns
Within the ForEach container place a script task that has ReadWriteVariables or Read Only Variables set to both the FirstName and LastName:
You may ask why access is not given to the Names variable.  The answer is that the ForEach container accesses the Names object variable and iterates though each row placing the FirstName column in the FirstName variable and LastName column in the LastName variable and passes those variable, one at a time, to the script task so access is not needed to the object.
The C# script task simply calls the MessageBox Show method to display the names one at a time:

MessageBox.Show(“The name is: ” + Dts.Variables[0].Value.ToString() +” ” + Dts.Variables[1].Value.ToString());

Script Task

 Another way to work with the variable is directly within a script task.  Since the script task will now be iterating through the Names object variable directly the variable needs to be assigned to the script tasks ReadWriteVariables or ReadOnlyVariables:

The C# script task first creates an OleDbAdapter and a data table and calls the OleDbAdapters Fill method to populate a data table with the Names object variable.  Once the data table is populated a foreach loop is used to iterate through the results and display the results using the MessageBox Show method.

 //Populate a data table with the Names Variable
OleDbDataAdapter A = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
A.Fill(dt, Dts.Variables[“User::Names”].Value);

// Iterate through the data table
foreach (DataRow row in dt.Rows)
string FirstName;
string LastName;
object[] array = row.ItemArray;
FirstName = array[0].ToString();
LastName = array[1].ToString();
MessageBox.Show(“FirstName=” + FirstName + ” AND LastName=” + LastName);

 The sample package outlined can be downloaded here.

I cover this method in my SSIS End to End videos from SSWUG. Registration for the videos can be found here and you can use VIP Code E2E10 to get $10 off the class.


All About SSIS Variables Part 1

sqlsafety 0 Comments

Variables have been available in SSIS since first introduced in 2005. So what exactly are they for and what can you do with variables? Glad you asked!! Variables can be used for a number of different purposes:
1. Define the path of a package
2. Hold system information, such as error information
3. Provide a means to create custom logging
4. Hold result set(s) to iterate through

Let’s begin with a simple example such as using a variable to define the path of a package. Consider that you are having to execute a data flow task and based on the number of rows loaded will dictate what task should occur next. This is simple enough to using a variable and precedence constraint. The first step is to create a variable, which is done in the variable pane in SQL Server Data Tools:




The first thing to note from the variable pane is the scope.  The scope defines where the variable will be available.  For example the scope for my variable “RowCount” is set to my package which I have named RowCount.  This means that any task within the package, including child packages, will have access to the variable.  It is best practice to insure that the variable scope is limited to only where it is needed.  This package will have a total of three tasks:

1. Data flow task that will take the data from the Adventureworks2012.Person.Person table through the Row Count transformation and to a flat file destination

2. A script task that executes if the row count is greater than 1,000

3. A script task that executes if the row count is less than 1,000

In this case the scope is set to the package since all 3 tasks will need access to the variable.  If the package contained other control flow tasks that did not require access to the variable then scope could be limited by placing the three affected tasks in a sequence container and setting the scope to the container.


Data Type

The data type defines the type of object and the acceptable values that the variable can be held.  Another important factor is to assign the most efficient data type for a variable.  For example, if the variable will hold a numeric value between -128and 128 the most efficient data type is SByte rather than UInt, which can hold a numeric value between o and 65,535.  MSDN documents the data types here.



This holds the value of the variable.  This value can be changed within the package using script task, expression task, execute SQL task, as well as several other.



The expressions provides a means of assigning a value to the variable using an SSIS expression.  Again this can be changed later within the package, but once package execution begins and the variable scope is  invoked the value or value from the expression provides the variable value until and if it is changed.

With the variable configured now I will include my data flow task that will record the value of all rows that go from the source, Adventureworks2012.Person.Person, to the destination, to the flat file destination using a row count transformation.  This is easy enough to do as once connecting the data source to the row count you will be prompted for the variable that will be used to hold this numeric value.



Precedence Constraint

Keeping in mind the requirements we now must define the path of execution based on the success of the data flow and the number of rows processed, which is now held within our variable.  By connecting both script tasks using our On Success precedence constraint we can then change both constraints to use Expression and Constraint we can define one constraint to use the expression @[User::RowCount] > 1000 and the other @[User::RowCount] < 1000.  This method will leave one path uncovered, that is if the RowCount == 1000.  In this case we are not concerned about that so we will let it ride.


Each script task will be used to access the variable and display it in a message box and then reset the variable to 0.  This requires that both tasks will need to have read and write access to the variable.


I will use VB.NET in each script task using the following code:
<style=”font-size: 12px;”>
Public Sub Main()

MessageBox.Show(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString)
Dts.Variables(“RowCount”).Value = 0
(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString)

Dts.TaskResult = ScriptResults.Success
The final package in the control flow looks like this:


You will notice that the precedence constraints are green, meaning that the path will only be taken if the preceding task completes successfully.  You will also note that there is an “fx” next to each precedence constraint which means that the constraint is also based on a precedence.

This is obviously a very simplistic example, but the first in the series so we progress with each new post.

The sample package, completed in SSDT 2012, can be downloaded here.


Fun With The ExecValueVariable Property

sqlsafety 0 Comments

              The sample package displayed in this post can be downloaded here.  In addition to the tasks noted there are several other tasks that are used to demonstrate the ExecValueVariable property.

              While exploring options to ease custom logging and reporting I came across an interesting, confusing, and poorly documented property that is available in several control flow tasks.  I was hoping to find a means to simplify capturing rows that were passed through a data flow without using a row count transformation of rows affected within an execute SQL task without using a result set or output parameter when I found the ExecValueVariable property.  I was pretty fired up and thought that I had discovered the silver bullet that would meet all my custom logging needs.  As I began to examine this property I found that it accepted a user variable and immediately thought that it would capture the affected rows or actions of the task. 

               I configured a data flow task’s ExecValueVariable property with a variable, called ExecValueVariable, of a data type integer and fired it off believing that the end result would be variable containing the number of affected rows from the task.


              I was a bit dismayed to receive an error, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  After this minor setback I began my internet research to find out what I had done wrong.  I came across a blog post here which gave me some more insight as to what this property does and does not do.  Basically the ExecutionValueVariable can be used to capture the value of rows or objects affected, but the task must have defined the ExecValue.  There are several tasks that implement the ExecValue and allow you to assign a variable to capture this value.

SSIS Control Flow Task

Value Captured

Execute SQL Number of rows affected
File System Number of files/Folders affected
Transfer Jobs Number of jobs moved
Transfer Error Messages Number of error messages moved
Transfer SQL Server Objects Number of objects moved


              Armed with this information I wanted to test the limits of this property and see exactly how it works so I began with an execute SQL task and a message box.  I used both DDL and DML statements in the execute SQL task to see how the ExecValueVariable would handle this:

col1 INT);


                                                  ExecValueVariable configurationXQTSQLVarProp 
                                                             Control FlowExecSQLValVariable
              Executing the task the result is what would be expected, a message box showing 1, the number of rows affected in the execute SQL task.  I edited the execute SQL task and added SET NOCOUNT ON in the top of the script and again executed the package.  The execution of the task succeeded, but the message box returned with a value of -1.  This demonstrates that the ExecValue is using the @@ROWCOUNT to assign the value of the variable and absent a @@ROWCOUNT the value returned is -1.

                 I was feeling pretty good about myself now so decided to experiment a bit with the ExecValueVariable again with a data flow task.  If you look at the chart above that outlines the tasks that support this property you will obviously notice that the data flow task is not mentioned, but IT STILL has this property.  I figured it was there for a reason right?!?!

              In my data flow task I used a source to the AdventureWorks2012 database with a simple query:

SELECT FirstName,
FROM Person.Person

I then used a flat file destination for the query results and configured the ExecVariableValue once again. I directed the on failure precedence constraint to another script task to display the variable value in a message box.


                                                      Control flow task CtrlFlowExecVariable
                                            Data flow task with source and destinationDataFlowName

              The results are exactly what I expected.  The data flow task failed and the message box displayed the default value assigned to the variable in the ExecValueVariable.  The reason for the failure is made apparent in the error message I shared above, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  The component must use the ExecValue method to assign a value to the placeholder variable in the ExecValueVariable.

              Unfortunately there is no way to configure this within a dataflow task, but using a script task we can demonstrate implementing the ExecValue.  I added two script tasks in my package and configured the on success precedence constraint connecting the two.  The first script is used to set the ExecValue property and use a message box to display the user defined variable ExececutionVariableValue of the task.  The ExecValueVariable property in the first task configured to use the ExececutionVariableValue variable:

Dts.ExecutionValue = 9999;

MessageBox.Show(“Assignment of ExecVariableValue occurs AFTER task completion. The current value is : “ + Dts.Variables[“ExecValueVariable”].Value.ToString());
 The second script task displays a message box that displays the new assigned value.

MessageBox.Show(“Assignment Complete: “ + Dts.Variables[“ExecValueVariable”].Value.ToString());

 Executing the package displays the message boxes:


Using a ROWVERSION Data Type Variable as an SSIS Input Parameter

sqlsafety 0 Comments

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

             I recently posted about retrieving and using a TIMESTAMP data type in a variable in SSIS.  I first want to apologize for any confusion between the ROWVERSION and TIMESTAMP data type.  ROWVERSION is the synonym for TIMESTAMP, and you should use ROWVERSION in your DDL statements wherever possible.  This outlines how to retrieve the binary value and converting it to a VARCHAR, using a system defined function, and stores it in an SSIS variable insuring that the binary representation is maintained.  I further showed how a dynamic query can be created using a variable expression using the ROWVERSION stored in the variable. 

              This works well enough until you attempt to use an execute SQL task creating a query that uses the ROWVERSION variable as an input parameter.  I was at first completely taken aback at the errors I was receiving.  By replacing the SQL string with the input parameter with a variable expression and concatenating the ROWVERSION variable to the expression string works so WHAT IS THE PROBLEM!!

To best illustrate this lets walk through the process. 

  • A string variable is used to hold the ROWVERSION
  • An Execute SQL task is used to get the ROWVERSION mapped to the variable as either an output parameter or a result set
  • The result set or output parameter MUST have a comparable SQL data type
  • ROWVERSION is a binary data type that does not provide an easy conversion
  • The system function, master.dbo.fn_varbintohexstr(rowversion), is used to convert ROWVERSION from binary to its equivalent VARCHAR value
  • Once the Execute SQL task completes the VARCHAR ROWVERSION value is stored in the SSIS string variable

              The difficulty working with this value is not within SSIS, but rather in SQL as the value must first be converted to a SQL data type that can be stored in the comparable SSIS variable data type.  Once the accurate value is retrieved then it is stored in the variable and it truly is what it is, a string representation of that binary value.

              When creating a query variable the entire query is a string and can concatenate SSIS variables together, conversions to string must be done where necessary.  The query is passed, from SSIS to the defined connection, as a string.  Once the string is received then SQL treats it as a query in total and parses the whole and not the parts.

SSIS Query Variable Expression

“SELECT * FROM ExampleTable WHERE [timestamp] = ” +  @[User::TimeStamp]

What SQL Sees

SELECT * FROM ExampleTable WHERE [timestamp] = 0x00000015E1CE4950

               Comparing a query variable to a query using input parameters the difference lies in the fact that SSIS variables are mapped to “?” within the query string.  Each input parameter must be mapped to an SSIS variable that MUST have a defined SQL data type that is comparable to the SSIS variable data type. 

The query is no longer treated as a string, but rather more like a stored procedure with input parameters.

SSIS Query DirectInput

SELECT * FROM ExampleTable WHERE [timestamp] = ?

What SQL Sees


SELECT * FROM ExampleTable WHERE [timestamp] = @TimeStamp

              SQL server evaluates the query and sees that a VARCHAR() value is being used in the predicate to compare the value of a ROWVERSION column and now we have an implicit conversion error.

Msg 257, Level 16, State 3, Line 2

Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.

              One way around this is obviously to port the query over to a variable that uses an expression, as outlined above this will work.  If you still wish to use DirectInput for your query string then you must make the conversion where necessary, BUT YOU MUST INSURE that the proper binary value is converted from VARCHAR to BINARY.

               The new DirectInput query string should look like this:

DECLARE @rv VARCHAR(25) = ‘0x000000000007A904’

SELECT * FROM ExampleTable WHERE [timestamp] = @RowVers

I have used two different variables just to try to step through the process and outline the conversion.  The @RowVers variable is a VARBINARY(8) data type and then used to hold the converted ROWVERSION data type.  You will notice that the CONVERT function is passed the optional style parameter of “1”, which in this case is specific to the binary style.  The new query insures that an explicit conversion is done to the input parameter, which is defined as a VARCHAR data type, to a binary format that can then be used in the predicate to compare the input value against the ROWVERSION column.

Limit SSIS Package Execution At Any One Time

sqlsafety 5 Comments

           It is a common requirement to insure that only a single instance of an SSIS package can be running at one time.  This would appear to be simple and my first idea was to check to see if a package assigned to an SSIS step in a SQL agent job is running by querying the msdb database.  OOOOPPPPPSSSS!!  I forgot!  SSIS packages can be run using several methods including SQL Server agent.  What if a job is being run from an SSIS package, batch file, and directly from the command line?  The msdb database, and even the SSIS catalog for SSIS 2012, does not capture when a package is run outside of a SQL Server agent job. 

               After some consideration I initially considered using .NET to query the running services looking for DTExec and attempt a WMI query to obtain the running package, but I realized that I was making this much harder on myself.  In the Microsoft.SqlServer.Dts.Runtime.Package.Applcation namespace there is a method, GetRunningPackages, which can be used to return metadata about each running package on the server/workstation in which the method is run. 

               To demonstrate how this works create a package that contains a package scoped variable called IDCount that is an integer data type.  Drop a script task onto the control flow design surface that uses assigns the IDCount variable as ReadWrite and the System:PackageID as a Read variable. 

              My current code sample uses VB.NET, but I will update this to include C# in the near future.  The script task code will utilize the GetRunningPackages method to iterate through all running packages and when the packageid of the running packages matches the current packageid then the IDCount variable will be incremented by one.
Public Sub Main
‘ Declare and instantiate objects here.
Dim app As Application = New Application‘…
‘ Create a RunningPackages collection, named pkgs, and fill it
‘ with the running packages from the application object.
Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)

‘ Enumerate over each package in the collection and display some data.
For Each package As RunningPackage In pkgs
If package.PackageID.ToString().ToUpper = Dts.Variables(“PackageID”).Value.ToString().Replace(“{“, “”).Replace(“}”, “”) Then
(“IDCount”).Value += 1
Catch ex
As Exception
End Try

End If

MessageBox.Show(“There are ” + Dts.Variables(“IDCount”).Value.ToString() + ” running”)
Dts.TaskResult = ScriptResults.Success
End Sub

           The short translation is that a new DTS application and runningpackages is instantiated in the code and then, using a for each loop, the packageids are retrieved and compared against the current packageid.  You will notice that the Replace and ToUpper functions are applied to insure that the package GUIDs match when compared.  If the packageid’s are equal then the IDCount variable is incremented by one. 

          The script task acts as the gateway to the package and a precedent constraint can then be used to insure that if the package is already running the package exits.  In order to exhibit how this works drop another script task onto the control flow design pane that accesses the IDCount as a read only variable.  In the code of the task simply call a messagebox show method to display the IDCount:
MessageBox.Show(“There is ” + Dts.Variables[0].Value.ToString() + ” instance of this package running”;

           Using a precedent constraint from the gateway script task to one or all other tasks you can evaluate the IDCount to insure that the package is not currently running, if the IDCount is > 1 then there are multiple instances of the same package running.


          When the package is executed the first script task evaluates what packages are running and compares the packageid’s.  The IDCount will always be at least one, but if more the precedent constraint evaluates as to whether the next step(s) should be execute.  If the count is not equal to one than the package stops at the precedent constraint.

          The sample package referenced in this example can be downloaded here.

Assigning SQL TIMESTAMP To An SSIS Variable

sqlsafety 5 Comments

            I know that my posts seem to have become quite brief, but since moving my blog I have been posting all sample files for download.  I have done my best to insure that all downloads fully document the steps and processes I discuss in each post.  The sample files for this post can be downloaded here.  

            I recently had to modify a package in order to retrieve and later use a TIMESTAMP.  The job specifics were that I had to retrieve the current TIMESTAMP, using @@DBTS, and later use that in an execute SQL task to store the value to filter future data sets in several data flow tasks.

              As I jumped to the task I quickly decided that the easiest way to capture the value was to use an execute SQL task and assign the value to an SSIS variable of a data type string.  I used the query SELECT ? = @@DBTS and mapped the package variable as an output parameter, data type of VARCHAR, in the execute SQL task. 


     I was surprised to find that the SSIS variable was assigned an empty value upon completing the task.  I verified the results by using a script task that used the TimeStamp variable value in a message box?  I was thoroughly confused and decided that it must be the fact that the value was being assigned as an output parameter rather than a result set.  After some quick modifications to the execute SQL task I assigned the result set of the query to the TimeStamp variable and once again used a script task to display the results.  I was once again surprised to find that the results were less than accurate.  The message box with the value is show below:


               This appears to be a good start, but when verifying the result I found that this value was once again short of the actual value of 0x0000000000069F6C.  After brushing up on the TIMESTAMP data type I discovered that this is actually a binary value and cannot be directly converted to VARCHAR, which is the data type of the parameter or result set.  I immediately began to explore using a script task with a data reader to obtain the value and provide an accurate conversion, but did not wish to make this package all the more complex.

               After some more research I discovered a built in function that will provide the conversion of a TIMESTAMP to NVARCHAR.  After some experimenting I discovered that the function master.dbo.fn_varbintohexstr provided me with the results that I wanted assigning an accurate value to my SSIS variable.  As any good child I wanted to take this function apart to see how it works so I used the OBJECT_DEFINITION() function to view the functions body.  I was surprised to see that this function actually calls another function, sys.fn_varbintohexsubstring, which does the heavy lifting behind the scenes.

               I modified my query in the execute SQL task to use the scalar valued function, master.dbo.fn_varbintohexstr, and assign the returned value to my SSIS TimeStamp variable.  I then was able to create SQL command variables that utilized the TimeStamp variable in several execute SQL and data flow tasks that used built dynamic commands in SSIS.

               I created a sample package that demonstrates the processes that I outlined which can be downloaded here.  I have also posted on using SSIS variables to replace dynamic SQL which helps in understanding the processes described.


SSIS Variable Expression Limits

sqlsafety 0 Comments

     I recently blogged about using SSIS variables in data flow or execute SQL tasks, the post can be found here.  I received a couple of questions about this technique specifically regarding the limit of 4,000 characters in a variable expression.  The first thing I want to clarify is that this character limitation is not a limitation for SSIS string variables, but only a limitation in regards to the variable expression.  Consider an outrageously large query such as this:

SELECT HumanResources.EmployeePayHistory.*, Person.Person.BusinessEntityID AS Expr1, Person.Person.PersonType, Person.Person.NameStyle, Person.Person.Title,
Person.Person.Demographics, Person.Person.AdditionalContactInfo, Person.Person.EmailPromotion, Person.Person.Suffix, Person.Person.LastName,
Person.Person.rowguid, Person.Person.ModifiedDate AS Expr2, Sales.PersonCreditCard.BusinessEntityID AS Expr3, Sales.PersonCreditCard.CreditCardID,
Sales.PersonCreditCard.ModifiedDate AS Expr4, Person.PersonPhone.PhoneNumber, Person.PersonPhone.PhoneNumberTypeID, Employee_1.BusinessEntityID AS Expr5,
Employee_1.NationalIDNumber, Employee_1.LoginID, Employee_1.OrganizationNode, Employee_1.OrganizationLevel, Employee_1.JobTitle, Employee_1.BirthDate,
Employee_1.CurrentFlag, Employee_1.SickLeaveHours, Employee_1.VacationHours, Employee_1.SalariedFlag, Employee_1.HireDate, Employee_1.rowguid AS Expr6
FROM     Sales.SalesOrderHeader INNER JOIN
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN
Sales.SalesOrderHeaderSalesReason ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderHeaderSalesReason.SalesOrderID INNER JOIN
Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID INNER JOIN
Person.Person INNER JOIN
Sales.PersonCreditCard ON Person.Person.BusinessEntityID = Sales.PersonCreditCard.BusinessEntityID INNER JOIN
Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID INNER JOIN
HumanResources.Employee AS Employee_1 ON Person.Person.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
HumanResources.EmployeeDepartmentHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID INNER JOIN
HumanResources.EmployeePayHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID ON Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND
Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID INNER JOIN
Production.ProductInventory ON Production.Product.ProductID = Production.ProductInventory.ProductID AND
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
Production.Product.ProductID = Production.ProductInventory.ProductID CROSS JOIN

The above query obviously exceeds the 4,000 character variable expression maximum, but it is still possible to use an expression to contain the entire query.

     To demonstrate this create three variables all of a data type of string.


Paste half of the query into the expression of variables Part1 and Part2 enclosing it within double quotes.  In the expression of the Whole variable use the expression:

@[User::Part1] +  @[User::Part2]

    You can validate the variable values by dragging a script task from the toolbox onto the control flow design tab and configure it to use all three variables at read only variables:


Use three different message boxes to display the values of each variable:

      Executing the package displays three different message boxes, Part1 and Part2 which contain half of the query and Whole, which contains the entire query:







     It is obvious that the character length of the Whole variable exceeds 4,000, which proves that the limitation of character size is specific to the expression and not the variable value.  I know that this seems quite inconvenient if you are working with a long and complex expression for a variable value, but unfortuntaely, this is the only work around avaialable.  This drawback has been posted on Microsoft Connect, but unfortunately has been closed as “By design“. 

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

Hit Counter provided by Sign Holders