Variables Window

Use the Variables window to create and modify user-defined variables and view system variables.
You can optionally display the Variables window by mapping the View.Variables command to a key combination of your choosing on the Keyboard page of the Options dialog box. 


Add Variable

Add a user-defined variable.

Delete Variable

Select a variable from the list, and then click Delete Variable.

Show System Variables

Click to toggle between a list that includes system variables and a list that does not. System variables are predefined. You cannot add or delete system variables or modify their properties. This list is color coded. System variables are gray, and user-defined variables are blue. The list includes variable properties.

Show All Variables

Click to toggle between a list that includes all the variables in a package and a list that filters the variables. If the Show All Variables option is not selected, the list includes only the variables in the scope of the package or the selected container or task; otherwise, the list includes all variables. If the Show System Variables option is selected, the list includes system variables. System variables are predefined. You cannot add or delete system variables or modify their properties. This list is color coded. User-defined variables are blue and system variables are gray. The list includes variable properties.

Choose Variable Columns

Click to open the Choose Variable Columns dialog box where you can change the column selection. Use the Variable Column Chooser dialog box to select the columns whose properties will display in the Variables window.

 

Scope

Select to display the scope of variables.

Data type

Select to display the data types of variables.

Value

Select to display the values of variables.

Namespace

Select to display the namespaces of variables.

Raise event when variable value changes

Select to display Booleans that indicate whether the variables raise the OnVariableValueChanged event.

Name

View the variable name. For user-defined variables, you can update the variable name.

Scope

View the scope of the variable. A variable has either the scope of the entire package, or the scope of a container or task. The scope of the variable must be sufficient so that the variable is visible to any other tasks or components that need to read or set its value. Scope cannot be modified in the Variables window, but depends on the item that was selected in the designer when you created the variable.

Data Type

View the data type of the variable. For user-defined variables, you can select a data type from the list.

Value

View the variable value. For user-defined variables, you can update the variable value. This value can be a literal or an expression.

Namespace

View the namespace name. User-defined variables are initially created in the User namespace, but you can change the namespace name. To display this column and change the namespace, click Choose Variable Columns and select the option in the Choose Variable Columns dialog box.

Raise Change Event

Indicate whether to raise an event when a value changes. By default, the Variables window does not list this column. Click Choose Variable Columns and select the option in the Choose Variable Columns dialog box.

IncludeInDebugDump

Indicate whether the variable value is included in the debug dump files.
For user-defined variables and system variables, the default value for the InclueInDebugDump option is true.
However, for user-defined variables, the system resets the IncludeInDebugDump option to false when the following conditions are met:

• If the EvaluateAsExpression variable property is set to true, the system resets the IncludeInDebugDump option to false.
  To include the text of the expression as the variable value in the debug dump files, set the IncludeInDebugDump option to true.
• If the variable data type is changed to a string, the system resets the IncludeInDebugDump option to false.

When the system resets the IncludeInDebugDump option to false, this might override the value selected by the user.

Using SQL Server Configurations to configure Flat File Connection Manager

The following is an example on how to use SQL Server based configurations to configure a Flat File Connection Manager’s ConnectionString property. We will store in an SSIS variable the directory that the Flat File Connection Manager will store/read its file to/from. The file name in this example will be based on another variable. We will use SQL Server configurations to dynamically manage the directory in this case, though you could/should do both.

• Create a new package.

• Create a new string variable, “Directory” and give it a default value of “C:\”

•  Create another new string variable, “FileName” and give it a default value of “test.txt”

NOTE: You don’t need the quotes when typing in the values for the above variables

•    Right click on the Control Flow, select “Package Configurations…”

• Check the box to “Enable package configurations”

• Click “Add…”

• Change “Configuration type:” to “SQL Server”

• Click on “New…” on the right of the “Connection:” line to create a new connection.
• This will bring up a new window, “Configure OLE DB Connection Manager”. Click “New…” here.
• Enter in the required information on this screen such as server (“localhost” in my case) and database name (“AdventureWorksDW” in my case). I am using “Windows Authentication”
• Click “OK”
• Click “OK” again

• Now we should be back to the “Package Configuration Wizard” screen. Click “New…” on the right of the “Configuration table:” line to create a new configuration table (if you don’t already have one created.)
• This brings up a “Create Table” screen. Leave the SQL alone, unless you want to change the name of the table it is trying to create. (Which I do — I replace the space with an underscore: “SSIS_Configurations”) Click “OK”
• Back on the “Package Configuration Wizard” screen, you can type in a “Configuration filter:” value. This is basically a WHERE clause when SSIS tries to query the table from above. On the next screen, we’ll get to choose which properties we’d like to configure via this method, so give this a unique name for this test package. I used “test_package”.
•   Click “Next >”

• Collapse all branches on this next window in the “Objects” pane. Make sure that nothing is checked. This is easy to do by checking the top level object (the name of your package) and unchecking it again. Expand the “Variables” sub-tree. Here you’ll see the two variables we created from before. Expand the “Directory” variable. Expand its “Properties” branch. Check the “Value” property under the “Directory” branch. At this point, you should only have one item checked.
• Click “Next >”
• Give this configuration a name. This is only used to help you identify which configuration is which in this package, as you can have more than one at any given time. I used “Directory Configuration” in this case. Click on “Finish”
• Click on “Close”

•           From here, we have the configuration setup, which will dynamically set the value of the “Directory” variable based on whatever value is in the configuration table. Now let’s set it up on a Flat File Connection Manager object.

·          

•  In the “Connection Managers” pane at the bottom of the window, right click and select “New Flat File Connection…”
•  Give it a “Connection manager name” of something descriptive. I used “My Test Flat File” and left the “Description” property blank.
• Type in the name of the file, fully qualified. I used “C:\test_placeholder.txt”
•  I used the default options of “Format: Delimited” and “Text qualifier: ” and “Header row delimiter: {CR}{LF}” and “Headers rows to skip: 0″
• Click on the “Advanced” tab on the left.
• Click on the “New” button at the bottom to add a new column. Click on it again to add a second column.
• Click “OK”

Now the Flat File Connection Manager has been setup, though it is not configured to use the variables from before. Let’s do that now.

•  Right click on the Flat File Connection Manager object in the “Connection Managers” pane and select “Properties”
•    Click on the “Expressions” property. If you have installed SQL Server 2005 Service Pack 1 or greater, you should now see some ellipses (…) on the right. Click on them.
• You should now have the “Property Expression Editor” showing. In the left side, click and select the “ConnectionString” property.
•   Click on the ellipses here as well to bring up the expression editor for this property.
• Use the following for your expression: @[User::Directory] + @[User::FileName]
• Click “OK”
• Close the properties window if you want.
• Now we have the Flat File Connection Manager object configured to use our variables, which will get their values from our database table. Let’s put it all together in a package we can run.

• Add a Data Flow task to the Control Flow
• Double click on the Data Flow task to edit it.
• Add an “OLE DB Source” component by dragging it to the Data Flow surface.
• Double click on the “OLE DB Source” component to edit it. The “OLE DB Connection Manager” property should already be populated for you and should be the “AdventureWorksDW” connection manager object we created from before.
• Change the “Data access mode” to “SQL command” as this is always good practice to do. This allows us to select only the columns from the database that we need and also allows us to add a WHERE clause if necessary. This is the only practice I recommend.
• Use the following SQL:
• SELECT DISTINCT FirstName, LastName FROM     DimEmployee
• Click on the “Columns” tab on the left. This populates the list of columns automatically that we want to expose to the Data Flow.
• Click “OK”
• Drag a “Flat File Destination” component to the Data Flow.
• Connect the two components using the green line from the “OLE DB Source” component.
• Double click on the “Flat File Destination” component to edit it.
• Again, just like the previous component, the “Flat File Connection Manager” property has been automatically selected for you.
• Click on the “Mappings” tab on the left to populate the mappings between the source and destination. Drag “FirstName” from the left to “Column 0″ on the right. Drag “LastName” from the left to “Column 1″ on the right.
• Click “OK”

Now, we’re ready to execute the package, but let’s understand a few things first. The value currently stored in the SSIS_Configurations table currently reflect the default value you established when you created the “Directory” variable. So, to show that this works, let’s change the value of the variable in our SSIS_Configurations table to something like “C:\TEMP\”. Also, make sure that you actually have a “C:\TEMP\” directory and that you have permissions to write to it. So the package has “C:\” as the value for “Directory,” but the SSIS_Configurations table has “C:\TEMP\”. You can update this value by running an UPDATE statement in Management Studio, or by opening the table directly by right-clicking on it in Management Studio. Either way, I assume you know how to do this.

Run the package! Note that when we initially setup the “Flat File Connection Manager” object, we gave it a file name of “test_placeholder.txt”. After the package executes, you should find a file, “test.txt” in the “C:\TEMP” directory, not the “C:\” directory as you have in your variable.

What’s going on here? Well, the package configurations are applied at run-time and configure the “Directory” variable to be the value stored in the table. We then use that variable along with the “FileName” variable to dynamically configure the “ConnectionString” property of the “Flat File Connection Manager” by using expressions.

Courtesy:www.ssistalk.com