Meta Database – SSIS Configurations Table and Framework Configurations

Ok, so this is not like the rest of the descriptions.  It is simply about the SSIS Config table, which is actually in the dbo namespace.  About the only thing different from the "stock" SSISConfiguraitons table is that I have set the Configured Value to be a nvarchar(4000).  I have done this because quite a few of my configurations are expressions or queries that would go well over the 255 character limit that is built in when you hit the "new" button in the configuration manager.  So, for review:

SSISConfigurations

The table used to set object properties via configurations in SSIS Packages.

One record per configuration filter per Package Path. (NOTE: this is not enforced.  If you insert multiple configuration filter / package path records the last one read from the table will be the one applied at run time; and of course their will be no way to tell which is applied 🙂 )

  • ConfigurationFilter – The filter used in the configuration lookup at package run-time when determining which configurations will be applied.
  • ConfiguredValue – The value to apply to the object property set in the package path.
  • PackagePath – The path to the property to set with this configuration.
  • ConfiguredValueType – The type of value to set with this configuration.

I am not going to talk about how you would populate the Configuration Table.  Instead, I will talk a bit about a few sample configurations used in my framework and why they are present.

Framework-AuditParameters-ServerName

The ServerName configuration is used to allow the easy identification of what server the configurations are coming from as well as (presumably) what server the ssis job was running from. It is further used in communicating back with the operator during error or completion emails.

  • server name variable value and description – Used as a helpful indicator of which server this package was run from (or at least, which server the configurations were applied from).

Framework-AuditQueryExpressions

The AuditQueryExpressions configurations are used to set the variable values which contain the sql command strings (via expressions) for the execute sql tasks within the pre and post processing sequence containers.

  • Create Audit Trail Variable Expression and Description – Builds the sql query which will execute the create audit trail stored procedure on the meta database.
  • ProcessingDateQuery Variable Expression and Description – Builds the sql query which will return the date range for this package to process.  If this package was not set up in the processing dates table, the minimum and maximum sql date time will be returned.
  • ProcessingDateUpdate Variable Expression and Description – Builds the sql query which will execute the update processing dates stored procedure.
  • StopProcessQuery Variable Expression and Description – Builds the sql command used to query the Audit Package Executions table to determine whether we have already run this package for this parent before.  If we have, most packages will exit before entering the pre-processing sequence container.
  • UpdateAuditTrail Variable Expression and Description – Builds the sql command used to execute the update audit trail stored procedure.  This stored procedure is used to update the various audit tables with the requisite information on the start and end dates, the records affected, etc.

Framework-EmailSettings

The EmailSettings configurations will hold the values for the from and to email addresses. It will also hold the expressions for the subject and body of the email when a package generates and error as well as for when a package executes successfully.

  • On Error Event Handler:  EmailBody_Error Variable Expression and Description – Builds the email body that will be sent out with an error email.  Holds the first error received as well as some audit information.
  • On Error Event Handler: EmailSubject_Error Variable Expression and Description – Builds the email subject that will be sent out with the error email.  Holds the package name and server information.
  • EmailBody_Completion Variable Expression and Description – Builds the email body used when the package completes.  Contains the package and server name as well as audit information on when the package started and finished, how many rows were inserted, updated, how many errors there were, etc.  Note that this email will usually only be sent out if there was some data error that was captured.
  • EmailSubject_Completion Variable Expression and Description – Builds the email subject that will be sent out with the completion email. 
  • EmailTo Variable Value and Description – Contains the list of recipient email addresses whom will receive the completion and error emails.
  • EmailFrom Variable Value and Description – Contains the email address of the entity sending the email.

Framework-IndexScriptGeneration

The IndexScriptGeneration configuration is used to house the expressions for the Create and Delete Index Script queries. Note, this is for the query that generates the individual scripts and not for the script itself.

  • CreateIndexesQuery Variable Expression and Description – Builds the query used to generate the create index scripts using the system tables.
  • DeleteIndexesQurey Variable Expression and Description – Builds the query used to generate the delete index scripts using the system tables.

Framework-RootFolder

The RootFolder configuration is used to house the UNC path to the folder which will contain sub folders for your log files, raw files, packages, access databases, etc.

  • RootFolder Variable Value and Description – The "Root Folder" is the folder which is at the base of the framework folder path.  The raw files, logs, packages, access database files, etc are all in a known location in relation to this folder.

SMTPConnectionManger-ConnectionString

The SMTPConnectionManager-ConnectionString is used to house the connection string to the local exchange server (or other mail service).

  • SMTPConnectionManager Connection String and Description – The connection string used to access the exchange server for email.

Controller-EmailSettings

This configuration contains the same information as the Framework-EmailSetting above, but contains different values as the audit trail on a control (or master or parent) package is usually different than that of a normal (child) package.

Other Notes

Once the framework configurations are set up, it is important to realize that other configurations can and should be set for the individual packages as applicable. The following are a few examples.

Control-META-PackageFolderLocation

As an example, I will usually put in a folder location that will point to the child packages.  This folder location is then used to build the connection string for each of the file connection managers used in the execute package tasks.

Dictionary-DynamicDatabaseConnectionString

As another example, I have added a connection string for the dynamic database that will be used when loading data in to the data dictionary.  When looping through the database list control table this value is set and the child package retrieves the correct information to populate the dictionary.

Advertisements
This entry was posted in SSIS and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s