Home > SSIS Best Practices > Use Dynamic Variable in SSIS

Use Dynamic Variable in SSIS

Date, or date range is very often needed for ETL process to bring data from host to SQL server. One way to do this is to use a dynamic variable in SSIS. The dynamic variable is first defined by querying the last load date, and then use the dynamic variable (will evaluated to a date or date range) in various other variables that define the actual queries on the host DBMS.

image

image

image

Access the Expression Builder from the variables Property sheet. This variable holds the actual query with the dynamic date.

image

"SELECT kkkkk,  SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 1, 10) || ‘ ‘ || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 12, 2) || ‘:’ || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 15, 2) || ‘:’ || SUBSTR(CAST( LOAD_TS AS CHAR(26) ), 18, 9) AS LOAD_TS FROM xyz WHERE
LOAD_TS > ‘" + @[User::MAX_LOAD_TS] + "’"

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: