Every entity in Dynamics 365, both OOB and custom, has a field named [Version Number]. It can’t be seen from the UI, as it is a system field, but it can be seen from when querying the database or when fetching data using FetchXML. This field is used by the system to track changes in a single record, and can be leveraged when doing data migrations/integrations to check if a record has changed from its source.
What is Version Number?
At the database level, Version Number is a rowversion data type (sometimes called timestamp).
Some key details from TechNet:
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.
So, to summarize, each time any field on this record gets updated, Version Number will change (tick up an increment).
How to track changes with Version Number
In the context of a one-way integration from one Dynamics 365 to a staging database, version number could be leveraged using SSIS by:
From the Source to Staging Table
- Check to see if the record exists in staging by comparing uniqueidentifier
- If record doesn’t exist, create the new record in staging table
- If record exists, compare Version Number
- If Version number is same, no action is needed (nothing changed)
- If version number is different, delete existing record in Staging and recreate with new attributes.
A dataflow might look like this:
Here, we stage with some records including the Version Number (account in this example). From there we do a lookup in our staging table, matching the accounted to see if it exists and passing the version number stored in the staging DB. If there is no match, we create the record. If there is a match we compare the version number from the source with staging. If these values are different, we delete the current record in staging, and recreate the record with what was queried from the source.
A thing to note, when querying the data directly from on-prem SQL, version number will be a binary data type and must be cast to a string to compare the values in the conditional split. However, if the data is being queried using FetchXML this value will be cast as an integer, but the functionality works the same way.
Why you would use Version Number
There are two main reasons why version number might be a good choice to track these changes. First, because it works at the database level, the version number will change even when a record is updated using a direct SQL update (not recommended or supported, but we’ve seen it happen none the less). Other values, such as modified on, will only update at the software level. Using version number will allow you to catch all changes, even unsupported ones.
The second reason is the simplicity of development. Most people here tend to think that development is simpler using this method, requiring you to log less information in auditing (much as modified on) allowing for more rapid and streamlined development.
Itching to learn more? Explore our blog!
Happy Dynamics 365’ing!