Using Data Lineage

DTS provides the ability to record data lineage, determining the source of any piece of data, and the transformations applied to that data. Data lineage can be tracked at the package and row levels of a table and provides a complete audit trail of data transformation and DTS package execution information in your data warehouse.

Data lineage is only available for packages stored in Microsoft® Repository. In Repository, lineage is an object related to a version of the DTS package object version. It contains both lineage values, the name of the person and computer executing the package, and the time of execution.

To implement data lineage:

• Design a plan for using data lineage information. The plan will vary depending on factors such as auditing needs, the amount of lineage data that can be managed, and performance considerations.
• Create the table columns in your data warehouse for receiving data lineage information. DTS maintains two lineage identifiers for each package execution:
  • A short (integer) value, which is the package version checksum
  • A long (uniqueidentifier) value, which is the globally unique identifier (GUID) of the package version

  Either or both of these values need to be written to columns in your data mart or data warehouse.

• Turn on the data lineage options in DTS Designer. These can be found on the Advanced Options tab of the DTS Package Properties dialog box.
• Map the data lineage source columns to destination columns in DTS Designer. This is done on the Transformations tab of the Transform Data Properties dialog box.
• Save packages containing the data lineage information to Repository. During the save operation, make sure to use the Scanning button to save the metadata information, and link the package to the Repository metadata.