Mod Package Audit Logging Data Model


Summary: The Mod Package application is used to iterate through SSIS Packages and add or edit design time properties. This process is very helpful in editing multiple packages with similar design time properties and saves a lot of time. With all these changes being made we need to keep track of what is being changed in a log. We have designed an Audit Logging structure that will allow us to keep track of what packages where modified, time constraints, and package modification details. This will give us consistent level of detail for each package and allows us to create useful reports.

The following shows us the objects that well using within SQL Server. We have designed Database Diagrams, Tables, Views, and Stored Procedures.

The following illustrates the database diagram ModPackageAuditLog that shows us the Entity Relationship Diagram for this model. This model is designed to track all the package information and details of the modifications.

The following describes the Audit Log data model:

ModPackageAuditLog – Parent table that gives us the package name and the time constraints for the modifications.

ModPackageType – look up table that describes the Type of Modification

ModPackageObject – Look up table that describes the Type of Object being modified

ModPackageAuditLogDetails – Child table that gives us the details of the modification inserting the type, object, name, property, old values, and new values

Table Design was to provide a relational model of a parent table and a child details table giving us a one to many relationship. The parent table is ModPackageAuditLog and the child table is ModPackageAuditLogDetails. The lookup tables are ModPackageType and ModPackageObject.

The following shows the table view structure of this model:

ModPackageAuditLog

ModPackageAuditLogDetails

Note: looking at the ModTypeId and ModObjectId we can see that they are numbers which relation to the lookup tables ModPackageType and ModPackageObject.

ModPackageObject

ModPackageType

Stored Procedures are used to insert and update the data in the tables. The benefits of using stored procedure versus the SQL itself are the Precompiled execution and Network bandwidth conservation. The following stored procedures were used in this data model:

Insert_ModPackageAuditLog_Record

Note: This procedure was used to insert the parent record in the ModPackageAuditLog table. This process is done when the package begins modifications and adds the current date time to the StartDateTime field. The procedure also gets the id related to this record which is called an identity and stores the value for the child table.

Insert_ModPackageAuditLogDetails_Record

Note: This procedure is used to insert into the child table called the ModPackageAuditLogDetails. The Id is used from the ModPackageAuditLog table to keep the relationship and the Type, Object, name, property and values are added.

EndDate_ModPackageAuditLog_Record

Note: This procedure is used to close out the EndDateTime field in the ModPackageAuditLog table. This is needed when the application ends its modifications to the package. This will give us an accurate representation of time from the start to the finish of the modifications.

A View was designed to give us nice visual of the data with the appropriate SQL in place. This allows us to see the lookups and relational object denormalized.

ModPackageAuditLog_vw


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