Framework Metadata Database Tables

The Framework stores all the configuration and logging data in a set of tables. It also maintains a codes translation table so there is transaparency to any codes used.

Framework version v1:

There are 7 tables used:

Logical Name Physical name Detail
Extract Limits dbo.Extract_limits Contains current extract limits
Framework Codes dbo.Framework_Codes Translation table of Framework codes
Package Errors dbo.Package_Error_Log Log of Package level errors
Package Master dbo.Package_Master List of all packages in the framework
Row Error Log dbo.Row_Error_Log Log of Row level errors
Run Log dbo.Run_Log Log of Run execution
Run Master dbo.Run_Master Run Master definition table
Tasks Log dbo.Tasks_Log Log of Task execution
Tasks Master dbo.Tasks_Master Task Master definition table

Extract Limits

Contains the current value of of any Extract Limits used. The data collected is the Type, Start and End of the Extract Limit. There is also a Notes filed for free format descriptions. Each limit is assigned an Id on entry into the table, which is the Id used to reference it in the Run and Task Master tables.

The Type, End and Start fields are all unvalidated nvarchar values that are intended to be interpreted by the Child Package at run time.

There is a default row with an id of zero for Runs and Tasks with no limits to apply.

Framework Codes

Contains translations for all codes used in the Framework tables. This is broken down by code type (e.g. "Task Status"), and provides a full text description of the code content.

Package Error Log

The error logging table that records all package level errors, such as connection failures. It captures the Task Execution Id and the SSIS error code and description.

Package Master

The master table that stores each unique package that can be referenced by the framework. It assigns a unique id to each package, and stores the path and name of the package.

Row Error Log

The error logging table designed to trap row level errors generated when processing data.

Run Log

The Run Log stores the execution details for each run, assigning each attempted run a Run Execution Id. It records the run status, start and end times and assigned Recovery Mode and Extract Limits.

Run Master

The Run Master defines each run, assigning each one a Run Id. It stores the Name, Extract Limit and Recovery Mode.

It also has a Y/N Running flag used to identiofy if the run is currently in progress.

Tasks Log

The Task Log stores the execution details for each task within a run, assigning each task a Task Execution Id. It records additional details about the task.

Tasks Master

The Task Master defines each task within a run.

Last edited Sep 12, 2010 at 8:58 AM by BI_Monkey, version 8


No comments yet.