SSIS variables - SQL Server 2008

SSIS variables allow you to store values for use during package execution. Variables can be used to update package properties during runtime, store integer values used for controlling container looping, provide lookup information, store values for use within a T-SQL statement or Script task, and build expressions. Figure shows the Variables window.

Variables window

Variables window

A variable is defined by its namespace (either system or user-defined). You can create new user defined variables, but you cannot create new system variables.

A variable is also defined by its scope, variable datatype, and value. Scope defines where the variable can be seen from within the package. A variable with package-level scope can be viewed by all objects within the package, whereas variables defined within a task’s scope can be viewed only by the task. Allowed variable datatypes include Boolean, byte, char, datetime, DBNull, double, int16, int32, object, sbyte, single, string, and Uint32.

Configuring Variables

To configure variables within your SSIS package, follow these steps:

  1. Within the SSIS Designer of a new SSIS package, right-click the Control Flow design surface and select Variables.
  2. The Variables window will appear. To view system variables, click the Show System Variables button (the one with the X in a gray square). System variables will now appear.
  3. To create a new user-defined variable, click the Add Variable button (which shows a function symbol with an orange star in the upper-left corner). This will create a user variable with the name Variable and a scope of your SSIS package.
  4. To create a variable scoped at the task level, click and drag a File System task from the Toolbox to the empty Control Flow design surface.
  5. To create a task-scoped variable (in this case, based on a File System task), simply create a new variable while the task is selected in the Control Flow design surface.

Now that you know how to configure variables, let’s run through creating a variable for a business scenario.

Creating Variables

Say that your company’s HR department has an application that uses the AdventureWorks table [HumanResources].[JobCandidate] to track job candidates in the queue. Once a week, the HR group wishes to evaluate the number of candidates in the queue, to determine whether or not to pull the job advertising campaign from the local newspaper.

In this example, you will create a variable called JobCandidateCount, populate it from the table, and e-mail an advertising update to HR based on the variable value.

  1. In a new package, add a new connection to the AdventureWorks database.
  2. Create a new user-defined variable called JobCandidateCount with a datatype of Int32.
  3. Drag an Execute SQL task onto the Control Flow design surface. Double-click the Execute SQL task.
  4. In the General properties, in the SQL statement section, change the Connection property to use the AdventureWorks connection. Change the SQLStatement property to use the following query:
    SELECT COUNT(*) as 'CandidateCount'
    FROM HumanResources.JobCandidate
  5. Change the ResultSet property to Single row.
  6. From the left navigation pane, select Result Set and click the Add button. Change the Result Name to CandidateCount. In the Variable Name field, make sure JobCandidateCount is selected. Click OK to exit the Execute SQL Task Editor.
  7. Create a new SMTP connection manager (use a valid SMTP server in your network or on your desktop).
  8. Drag a Send Mail task onto the Control Flow design surface. Double-click it.
  9. In the Send Mail Task Editor, under the General properties, change the Name property to Pull Advertising. Under the Mail properties, for the SmtpConnection property, select the SMTP connection manager. Since this is just an exercise, for the From and To properties, select your own e-mail address. In the Subject line, type Pull Advertising. This will be the notification to remove advertising if the job candidate queue exceeds ten candidates. Click OK.
  10. Copy the Pull Advertising task and paste a copy on the design surface. Double-click it.
  11. On the General tab, rename the Name property to Retain or Start Advertising. In the Mail properties, change the Subject property to Retain or Start Advertising. Click OK.
  12. Create a precedence constraint from the Execute SQL task to the Retain or Start Advertising task. Double-click the green arrow to configure the constraint.
  13. In the Precedence Constraint Editor, change the Evaluation operation to Expression. In the Expression field, type the following expression:
    @JobCandidateCount<11

    This expression evaluates your user variable, testing to TRUE if the variable is less than 11. Click OK. Notice that a small function symbol appears by the Retain or Start Advertising task.

  14. Create a precedence constraint from the Execute SQL task to the Pull Advertising task. Double-click the green arrow to configure the constraint.
  15. In the Precedence Constraint Editor, change the Evaluation operation to Expression. In the Expression field, type the following expression, and then click OK.
    @JobCandidateCount>10
  16. Select Debug ➤Start Debugging to test the package.

Since the candidate count is more than ten, you’ll see the Execute SQL task turn green, and then the Pull Advertising task will turn green afterward. The Retain or Start Advertising task is not run, as the variable value was not less than ten.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics