December 8, 2011 at 3:11 am
Case:
1) Source table with a column rowversion with data type TIMESTAMP
2) Target table with a column rwoversion with data type BINARY(8)
I want to load only the new and updated records from the source table into the target table, based on the rowversion.
Somehow, I can only save the max(rowversion) from the target table as a STRING in a variable with following code:
SELECT CONVERT(VARCHAR,MAX(ROWV),1) AS rowversion
FROM Target_table
This code is giving me the correct output in the debugger.
In the next step (OLD DB source editor), I want to compare the max(rowversion) from the target table with the rowversion in the source table in order to give me only the new and updated records. But I compare now a data type TIMESTAMP with a data type STRING. How can I solve this... I don't see the solution...
I'm working with SQL Server 2008 R2.
Thanks
December 8, 2011 at 3:34 am
I think it is worth mentioning you are also working with SSIS.
Why are you trying to store rowversion into a variable?
Wouldn't it be easier to do the comparison directly in the SQL query in the OLE DB Source?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 8, 2011 at 3:42 am
Hi Koen,
indeed, I'm working in SSIS.
Is it possible to do the comparison in the OLE DB source editor? Because my target table is stored in the BI environment, and the source table is stored in the transactional environnement...
December 8, 2011 at 4:39 am
The OLE DB Source editor is used just to select data. You can do a comparison by joining the two datasets (source and destination) on the business key using a MERGE JOIN and then a conditional split.
In the conditional split, you will compare the rowversion column of the source with the rowversion column of the destination and route the rows to its appropriate output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply