Data Dictionary

The need for a Data Dictionary in Data Migration

At a bare minimum the data dictionary needs to contain the following attributes

Source TableĀ 
Source ColumnĀ 
Source Data Type
Validation Rules.
Typical Values.
Transformation Rules.
Target Table
Target Column
Target Data Type.

optionally adding fields to the data dictionary for
Date changed
Revision Number
This can be implemented in many ways – depending partly on how public the data needs to be – for example, it can be stored as an excel workbook, an access database or in SQL server itself (possibly with an access frontend to allow simple updates to the underlying data)

It’s advisable to make the data dictionary publicly available – for example by publishing a report on the data dictionary or by making the access / excel data available.
In all cases, I would recommend change tracking – which lends itself more to the SQL server stored data even if it’s then made visible via Excel as a linked source to all relevant SME’s.

Common transformation rules could include
Cast as datatype
format datetime 112