SSIS SCD - Staging question

Question

I have a source table with PK as seqId and lastupdatedDate (datetime when last it was updated).
I have created a staging table as is from source table which will be truncate and load all time
I have a target table with all the fields from stage +Audit fields ofcourse lastupdateddate

I am using SQL Server 2016 and VS 2015 SSIS to perform this.

Using lastupdateddate I need to bring in only the change data in stage
I am capturing lastupdateddate in variable
I have build another variable @vdynamicQuery to capture entire select query in expression, here I am adding this @lastupdateddate variable in expression
Then I am using Data flow task to using SQL command from variable to bring in the @vdynamicQuery to get the result and populate in target stage table.

Is this approach correct or @vdynamicQuery may not work with dates ? I am still in btw the development here.

Answer 1

Thanks I am getting the expected result. I have used LastUpdatedDate from source > max(LastUpdatedDate) in target, to bring in incremental data in stage.

My only concern here was I see data getting inserted in faction of mill sec also, which was not getting handled in SSIS, I converted date to 23 char of varchar and then while comparing I changed it back to date, as of now it is working.