Miscellaneous Ramblings on Hacking

November 29, 2006

Variables in Source SQL

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 11:09 pm

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. croppercapture26.Png

  4. Under the Result Set tab, set the Result Name: ‘last_load_date’ to the defined variable. In my case, I named my variable: LastRunDate.croppercapture27.Png
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] + “‘;”

    croppercapture28.Png

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: