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 LimitsContains 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 CodesContains 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 LogThe 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 MasterThe 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 LogThe error logging table designed to trap row level errors generated when processing data.
Run LogThe 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 MasterThe 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 LogThe 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 MasterThe Task Master defines each task within a run.