SSIS SCD - Staging question

Neil 396 Reputation points

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
{count} votes

Accepted answer
  1. Neil 396 Reputation points

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful