The control tables within the meta database will not be uniform across the various instances running this framework. I envision this schema being used for the various job processing tables that direct workflow, enumerate list items, or other general information of this variety.
As examples, I have set up a control table for a database list to fill in the data dictionary, a processing dates tables to keep track of data ranges for the various packages / jobs, and a frequency types table as a normalized list of the various frequencies which the date ranges might occur in.
The dictionary database list table will be used to list the databases to be included in the data dictionary tables. This list contains enough information that a job or package can loop through it and gather information from the system in question. Currently, I have set up a package to gather the information from the SQL 2005 and 2008 databases within our environment. (I still need to go back and deal with the DB2 and SQL 2000 databases).
One record per database to be interrogated.
- DictionaryDatabaseListKey – The surrogate key used to uniquely identify the record in the dictionary database list table.
- DatabaseName – The name of the database in this database list record.
- DatabaseConnectionString – The connection string used to connect to the database in question. Usually this information would go in to the configuration table, but we want to be able to use a single set of generic packages to loop through each of the databases per database type and it seemed appropriate to just copy these values from this table to an entry in the ssis config table.
- DatabaseType – The type of database (SQL 2000, SQL 2005, SQL 2008, DB2, etc). Used as a filter when selecting the databases to iterate over during the import into the data dictionary.
- IsCurrent – Is this record current? Used to allow a database to be ignored.
- LastModified – When was this record last modified?
The frequency types table will hold a list of valid frequency types to be used in the processing dates table to ensure that no unknown frequencies are input.
One record per frequency type.
- FrequencyTypeKey – The surrogate key used to uniquely identify the record in the frequency types table.
- FrequencyType – A type of frequency to which a date range applies. (Week, Month, Quarter, Calendar, Month To Date, etc)
- FrequencyTypeDescription – A description of the frequency represented by this record.
The processing dates table is used to store a date range for which a package is supposed to processes data. It is interrogated when the package (or job) is starting and updated upon successful completion. It is possible that a single package can be processing multiple sets of data, one perhaps a daily or weekly operation while another might be aggregating data over a longer set of time. The processing dates date range is updated via stored procedure.
One record per frequency type per processing filter.
- ProcessingDateKey – The surrogate key
- ProcessingFilter – The filter used to define the record in question. Usually the package name concatenated with the frequency type.
- FrequencyTypeKey – The foreign key used to point to the value in the frequency type table.
- ProcessingStartDate – The start of the date range, as a date time, for which the package or job using this processing filter is supposed to occur.
- ProcessingStartDateKey – The start of the date range, as a smart key pointing to the Date Dimension, for which the package or job using this processing filter is supposed to occur. NOTE: This column is a computed column based on the ProcessingStartDate.
- ProcessingEndDate – The end of the date range, as a date time, for which the package or job using this processing filter is supposed to occur.
- ProcessingEndDateKey – The end of the date range, as a smart key pointing to the Date Dimension, for which the package or job using this processing filter is supposed to occur. NOTE: This column is a computed column based on the ProcessingEndDate.
Populating the Control Tables
The control tables will more than likely need to be populated manually (such as the database list and frequency types, as well as the initial population of the processing dates table). They may, however, be updated on a set process (such as the processing dates table).