Variables in Source SQL

When dealing with source database systems, it’s common to only want to process the “delta” of changed or new records. These systems typically have a created and last_updated datestamp on each record for audit purposes. This entry will detail how one can do this in SSIS.

Last Run Date

In my scenario, I opted to store a transaction log table. This table stores the package execution history. This table is then queried in a SQL Task in the control flow. The SQL Task is set to retrieve only one row and store the last_load_date into a variable. This variable will then be used later in the where clause of my source SQL in later data flows.

The creation script for the table is below.

CREATE TABLE dbo.load_history (
mapping_name nvarchar (50) NULL ,
last_load_date smalldatetime NULL ,
start_date smalldatetime NULL ,
end_date datetime NULL ,
status nvarchar (20) NULL
)

The setup of the SQL Task to return the last_load_date into a variable is done as follows:

1. Defined a variable LastRunDate with a data type of DateTime.
2. Under the General tab, set the Result Set to ‘Single Row’. The SQLStatement is set to:
   select last_load_date from load_history
   where mapping_name = <mapping_name>
   and end_date is null
   and status = ‘Pending’



3. Under the Result Set tab, set the Result Name: ‘last_load_date’ to the defined variable. In my case, I named my variable: LastRunDate.

The Hidden Magic of Variable Properties

Next, is to define a variable for the source sql.

1. Add a variable of data type string. In my case, I named the variable: SQL
2. With the variable selected in the variable window, hit F4 to open the variable’s properties
3. Set the property ‘EvaluateAsExpression’ to true
4. Set the property expression using the variable defined earlier. Don’t forget that the expression is a string, so it must be wrapped in double quotes. For example:“select col_1, col_2, … from table_1
   where
   last_update_date >= ‘” + @[User::LastRunDate] + “‘
   or created_date >= ‘” + @[User::LastRunDate] + “‘;”