Archive

Posts Tagged ‘DataSync’

SSIS – Use timestamp to detect changes

April 19, 2016 Leave a comment

Last week, a solution was required to build an SSIS solution that can pick up all changes from a database using the versionnumber (timestamp) and sync the changes to an external database using the timestamp of the database. This is quite easy to achieve by:

  1. Step 1, retrieve the previous timestamp. Storing the previous timestamp can be easily achieved by create a file using File.WriteAllText method.

 Dim runLastFile = System.IO.Path.Combine(currentPath, “LastRun.txt”)     System.IO.File.WriteAllText(runLastFile, lastRun)

I know it’s in VB but you can easily convert it to C#, since it’s an old solution i didn’t bother to convert it. If the file is not yet existing, you need to create a logic to get the lowest timestamp. Like this:

declare
@minVersion binary(10),
@minVersion_str nvarchar(42)

SET @minVersion = ( SELECT MIN(timestamp fieldName) FROM {sourceTable} )
SET @minVersion_str = upper(sys.fn_varbintohexstr(@minVersion))
SELECT @minVersion_str as LowestVersion

2. Step 2, get the current timestamp from the source database by using MIN_ACTIVE_ROWVERSION, the challenge however is to store this as string/text so the SQL query can be constructed later on in the process. After 15 minutes of googling I was able to find it somewhere (credit to the forum, lost the link).

declare
@currVersion binary(10),
@currVersion_str nvarchar(42)

SET @currVersion = MIN_ACTIVE_ROWVERSION()
SET @currVersion_str = upper(sys.fn_varbintohexstr(@currVersion))
SELECT @currVersion_str as CurrentVersion

3. Step 3, construct the SQL query using the previous timestamp. Something like this:

                         SELECT {sourceColumn}
FROM {sourceTable}
WHERE {timestamp fieldName}  >= @previousTimeStamp

4. Step 4, if the process is successful don’t forget to store the current timestamp.

5. Step 5, start from step 1.

Advertisements
Categories: Microsoft SQL Server Tags: ,
%d bloggers like this: