Summary: This application is designed to modify the design time properties of an SSIS package. The idea behind this application is that we find that we may need to do a lot of changes to multiple packages and we don’t want to have to open up each one and add several changes. This will allow the users to browse for a folder with SSIS Packages and run a modification procedure on each package in that directory and save the changes. The Procedure is written in the C# development language and is completely open to full customization for the modifications. This application will hopefully grow and have many features so the users can control the entire modification process from the GUI.
The following is a screen shot of the application and is a very simple Windows form. We have a “Browse” button that opens up a folder browse dialog. We also have a “Clear” button that will allow you to clear your directory entry in the textbox. The last button is “Run SSIS Mod Package” which will process the changes to all the packages in the directory specified.
The following is a screen shot of the SSIS Directory text box populated with a path to run the process. Once we choose the appropriate directory and execute the “Run SSIS Mod Package” then the application iterates through the directory looking for *.dtsx extensions and modifies the package’s XML.
Underneath the hood to show how this application works we have written C# functions and a class which are separated into regions based on the functionality. The c# code was broken into regions to help organize the code.
Run the Package Mod Function is the region containing the main function called RunSsisPackageMod. This function is used to open the package and allows for there to be additions or changes to the properties of the package. Along with that we have an Audit Log trail that inserting logging detail records into the a table.
Button Functions is a region containing all the OnClick events for the buttons on the form. The buttons on the form are Browse, Clear, and RunSsisPackage.
Audit Logging Functions is a region containing all the functions related to the ModPackageAuditLogging and ModPackageAuditLoggingDetails tables. These functions use the stored procedures created to access the table for inserts and updates.
The following are the functions that are used to accomplish this:
private static decimal InsertAuditLogHeaderRecord(Package package, SqlConnection sqlConn, ref SqlCommand cmd)
Note: The InsertAuditLogHeaderRecord function inserts the head log record with a start time and outputs the identity column for the detail records.
private static SqlCommand InsertAuditLogDetailRecord(SqlConnection sqlConn, SqlCommand cmd, decimal AuditLogId, int m_type, int m_object, string m_name, string m_prop, string oldval, string newval)
Note: InsertAuditLogDetailRecord inserts the detail records.
private static SqlCommand EndDateAuditLogRecord(SqlConnection sqlConn, SqlCommand cmd, decimal AuditLogId)
Note: EndDateAuditLogRecord closes the final process of editing the package by end dating the header record. This is used to keep track of the start and end times of the package modifications.