SSIS Basics: Introducing Variables - 1

https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/

SSIS Basics: Introducing Variables

08 August 2012

by Annette Allen

In the third of her SSIS Basics articles, Annette Allen shows you how to use Variables in your SSIS Packages, and explains the functions of the system-defined variables.

In previous articles in the SSIS Basics series, I showed you how to set up a SQL Server Integration Services (SSIS) package. I also showed you how to add a Data Flow task that contains the components necessary to extract data from a SQL Server database, transform the data, and load it into an Excel spreadsheet.

In this article, I will show you how to use variables in your SSIS package. Variables are extremely important and are widely used in an SSIS package. A variable is a named object that stores one or more values and can be referenced by various SSIS components throughout the package’s execution. You can configure a variable so its value is updated at run time, or you can assign a value to the variable when you create it.
There are two types of variables in an SSIS package: system and user-defined. SSIS automatically generates the system variables when you create your package. Components can then reference the system variables as necessary, usually for debugging and auditing purposes. You create user-defined variables as needed when you configure your package.

Setting Up Your SSIS Package

To try out the examples in this article all you need to do is to create an Excel spreadsheet named Employee, for the purposes of this article it can be an empty file. This does follow on from the previous article in this series “SSIS Basics: Adding Data Flow to Your Package” however it is adviseable to start from a new package.

Creating Variables

In the following sections, I will show you how to create a user-defined variable and reference it within your package. Then I will show you how to create an expression that dynamically updates the variable value at run time. Our starting point is where my previous article left off; however, all you need to do is create an Excel spreadsheet named Employee, I have saved it in the root of my d:\ drive but you can save it wherever appropriate, once this is created you should have no problems working through these examples.

The Variables Window

The easiest way to create a user-defined variable is by using the Variables window. When you create a variable, you specify a name, data type, and scope.
The scope controls which package elements can use the variable. You can define a variable’s scope at the package level or at the level of a specific container, task, or event handler. For example, if you configure a variable at the scope of a ForeachLoop container, only that container and any tasks within the container can use the variable. However, if you configure the variable at the package level, any component within the package can reference the variable. One of the most common issues to arise when working with variables is for them to have been inadvertently created at the scope of a container or task, when they should have been created at the scope of the package.
To view the Variableswindow, right-click the design surface on the ControlFlow tab, and clickVariables. By default, the Variables window appears to the left of the design surface, as shown in Figure 1.

Figure 1: Variables window in SSIS Designer

At the top of the Variables window, you’ll find five buttons (shown in Figure 2) and, beneath those, column headings.

Figure 2: Buttons at the top of the Variables window

From left-to-right, the buttons let you perform the following tasks:

• Adding a user-defined variable
• Deleting a user-defined variable (available when a variable has been created)
• Showing system variables
• Showing all variables
• Choosing the variable columns to display

By default, the Variables window displays the following columns:

• Name: The name assigned to the variable. When you first create your variable, the value Variable is used. If there is already a variable named Variable, a number is added to the name.
• Scope: The scope at which the variable should be set. The majority of the time, you can go with a package-level scope, which means the scope should be the name of the package. To select a different scope, you must select the applicable task or container when you create your variable. It is very easy to inadvertently create a variable at an unintended scope. Before creating a variable, be certain that the correct task or container is selected or, if you want a package-level scope, that no components are selected.
• DataType: The type of data that the variable can store. SSIS supports the following variable types:
  • Boolean
  • Byte
  • Char
  • DateTime
  • DBNull
  • Double
  • Int16
  • Int32
  • Object
  • SByte
  • String
  • UInt32
  • UInt64
• Value: The variable’s initial value. This can be populated here or left blank and populated at run time.

If you click the Choose Variable Columns icons, the Choose Variable Columns dialog box appears, as shown in Figure 3.

Figure 3: Additional columns available for variables

As you can see, the Scope, Data type, and Value columns are selected by default. You can also select one of the following columns to display:

• Namespace: Shows whether the variable is a system or user-defined variable.
• Raise event when variable value changes:Boolean (True/False) field that if selected will fire an OnVariableValueChanged event if the variable value is modified. This will be covered in more detail in a future article when looking at error handling and debugging.

You are now ready to create a variable.

Creating a User-Defined Variable

In this section, I demonstrate how to create a variable and assign a value to it, which in this case, will be a file path. Using the Excel spreadsheet created in the setup, I will save a copy of the spreadsheet to a new folder using a variable to re-name it.
To create a variable, click the white space on the Control Flow design surface to ensure that no components are selected, and then click the Add Variable button in the Variables window. A variable is added to the first row of the window’s grid, as shown in Figure 4.

Figure 4: A new variable in the Variables window

Notice in Figure 4 the blue button with the black X to the left of the variable name. You can use this button to delete the variable. Also notice that the scope is set to the package name, SSISBasics, which is the scope we want to use for this exercise.
By default, the variable is configured with the Int32 data type. However, for this exercise, we’ll use String. To change the data type, select String from the drop-down list in the Data Type column. When the data type is updated, the value in the Value column changes from 0 to an empty string.
You now need to enter the variable value. Simply type in the path name you plan to use for your Excel file. In this case, I used d:\Demo. I also changed the variable name to DestinationFilePath, as shown in Figure 5.

Figure 5: Configuring the new variable in the Variables window

You’re variable should now be ready to go. Because you created it with a package scope, any component in your package can use that variable.

Using the Variable

I am now going to show you how to reference the new variable from within a File System task. First, you must add the task to the control flow, as shown in Figure 6.

Figure 6: Adding a File System task to the control flow

Then double-click the File System task to open the File System Task Editor, as shown in Figure 7.

Figure 7: Accessing the File System Task Editor

You now need to configure the task’s properties. Table 1 describes how to configure each of these properties

Property	Description
IsDestinationPathVariable	Change to True.
DestinationVariable	Select the variable you created earlier from the drop-down list associated with this property. When the variable is added to the property, it is preceded by the namespace name and two colons (User::).
OverwriteDestination	If you want to overwrite the destination every time the package runs, select True, otherwise leave it as False. However, if you select False and run the package more than once, SSIS will generate an error. As the name of the excel spreadsheet will not change, I suggest you select True.
Name	The default name is File System Task. You can rename the task to better reflect what it does. For this exercise, I renamed the task Copy to new folder.
Description	You can type a full description here. I used Copy the Employee’s excel spreadsheet to d:\Demo\.
Operation	The default option is Copy File. Because that’s what we’ll do in this exercise, you can leave this option selected. But note that other options are available, and I will cover some of them in future articles.
IsSourcePathVariable	Leave this option set at its default value, False. Although we’ll be setting the destination to a variable, the source path will directly reference the original Excel file.
SourceConnection	This is where you select the connection to the source file you want to copy. From the drop-down list associated with this property, select as currently we have no connections set up. The Usage type: defaults to Existing File which is correct, as we have already created the spreadsheet, select the Browse button and browse to the Excel spreadsheet created called Employee.xlsx and click OK to accept.

Table 1: Configuring the File System task properties

Figure 8 shows what your File System Task Editor should look like after you’ve configured the properties. Review your settings, and then click OK.

Figure 8: Configuring properties in the File System Task Editor

The control flow should now show that the inverted red X has been removed from the FileSystem task and that the task has been renamed to Copy to new folder.
To verify whether the task works, run the package by clicking on the green arrow (the Run button) on the menu bar. Then open Windows Explorer and check that the file has been copied into the target folder correctly.