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.

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

"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] + "'"