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:
- 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.