Changing Variable Scope and other Enhancements

Changing Variable Scope and other Enhancements

Rate This
  • Comments 3

In SQL Server 2012, SSIS includes some very useful enhancements to package variables.

You can now change the scope of an existing package variable. Just select the variable in the Variables window and click the new Move Variable icon.

Then, in the Select New Scope dialog box, select the package, or a package container, task, or event handler, that you want as the scope of the variable. Click OK to save the change to the variable scope.

You can also assign an expression to the variable in the Variables window, rather than having to open the Properties window. Click the ellipse button that appears next to the new Expression column in the Variables window. The Expression Builder dialog opens. You use this tool to build, validate, and evaluate the expression.

After you've assigned an expression to the variable, a special icon marker displays next to the variable to signify this. In addition, the expression appears in the Expression column, and the result appears in the Value column.

 

This special icon marker also appears on connection managers and tasks that have expressions set on them. For more information about these Adorners and the new HasExpressions property, see Matt Masson’s Expression Adorners blog post.

 

There are a couple of items to note when you’re using the Variables window to assign an expression.

  • Before assigning the expression, you need to make certain that the variable data type is compatible with the evaluation result of the expression.  If the data type is not compatible and you try to assign the expression to the variable, an error message appears.
  • When you assign the expression, the EvaluateAsExpression property in the Properties window is automatically set to True.

For information about the SSIS system variables, including the new one for SQL Server 2012 (ServerExecutionID), see System Variables in Books Online. For more information about variables in 2012 SSIS packages, see Integration Services (SSIS) Variables.

 

 

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • We can change the scope of the variable in 2008 BIDS.PFB the steps I did for changing the scope of the variable from the package level to the task/container level.

    1)Right click on the package.dtsx file from the Solution explorer and click on the "View code"

    2)Search for the variable for which you wanted to change the scope. Copy the XML code related to that variable.

    3)Search for the control for which you wanted to use this variable and copy the xml code after completion of any tag(Many tags are related to a task/container).

    4)Remove the origial copied code.

    5)Save this file

    6)Open the package and the scope of the variable would have been changed

    I am not sure If it impacts the package or not, but it worked for me.

    Kindly let me know If it will impact the package or not if any one knows

  • We can change the scope of the variable in 2008 BIDS.PFB the steps I did for changing the scope of the variable from the package level to the task/container level.

    1)Right click on the package.dtsx file from the Solution explorer and click on the "View code"

    2)Search for the variable for which you wanted to change the scope. Copy the XML code related to that variable.

    3)Search for the control for which you wanted to use this variable and copy the xml code after completion of any tag(Many tags are related to a task/container).

    4)Remove the origial copied code.

    5)Save this file

    6)Open the package and the scope of the variable would have been changed

    I am not sure If it impacts the package or not, but it worked for me.

    Kindly let me know If it will impact the package or not if any one knows

  • This feature is in BIDS 2008 R2. It works in exactly the same way.

Page 1 of 1 (3 items)