Microsoft SQL Server

From Starfish ETL
Revision as of 18:35, 5 August 2016 by Jkuehlthau (talk | contribs) (Created page with "=Using rowversion fields= Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with Da...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Using rowversion fields

Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with Date/Time fields, but a better option is to use the MSSQL Row Version type of filed. This is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. See: https://msdn.microsoft.com/en-us/library/ms182776.aspx.

To accomplish this:

  1. Setup your origin to use the Rowversion field. Note that you must convert the rowversion field to the bigint field type. Example:
SELECT *, CONVERT(bigint, row_version) num_row_version  FROM TBL_Company
where CONVERT(bigint, COTIMESTAMP) > '@@VAR:LastRowVersion@@'
order by COtimestamp asc
  1. Retrieve the LastRowVersion with a Before Once Before Conn script. Example:
Function ScriptedVariable
	ScriptedVariable=XrefRead("LastRowVersion","Accounts")
End Function
  1. Set the LastRowVersion ewith an After Repeat Each Row script. Example:
Sub VBScriptProcedure
	If Not PreviewMode Then
		XrefWrite "LastRowVersion","Accounts","@@ORG:num_row_version@@"
	End If
End Sub