There are many advantages to setting up configurations for your SSIS packages. If you have configuration values set up throughout your packages you can avoid unnecessary retesting and deployment processes later on. It also makes the process of redeploying to multiple environments (Dev, UAT, SysTest, and Prod) a lot easier.
On a recent project my colleagues and I decided that it would be very useful to implement package configurations in our SSIS packages. After a little bit of investigation we realised that the out-of-the-box package configuration although useful would not provide us with what we required.
Our requirements were as follows:
• Multiple filtering options - Because a package can be used in multiple systems and multiple environments (Development, UAT, Production)
• System and Package variable - Some variables (e.g. warehouse connection string) maybe system wide variables whereas some may be unique for a specific package.
• Encryption of Sensitive Information- Some of the variables may contain sensitive information that we wanted to have encrypted in the database.
• Config Audit Log - An audit log of what configuration values were during the time the package executed.
• Config Audit Trail - An audit trail of who has changed the config values
• Simple implementation - an easy way to implement the config process throughout all existing and future packages.
Database
I am sure most SSIS implementation use an Audit database of some kind to capture package and task execution logs. (See a future blog on SSIS logging) This is a good place to store your configuration information as it goes hand-in-hand with your logging information.
As we are storing this information in a database which can be moved between servers with varying users it is advisable to have its connection string being set up using the out of the box SSIS configuration functionality.
The database contains configuration tables i.e. PackageConfig, PackageAudit, PackageConfigLog. The PackageConfig table contains information uniquely defining each package through it’s package GUID and each of the package variables, variable types and variable values along with a flag whether the value should be encrypted or not. You should also include multiple columns for filtering your configuration values by i.e. System, Environment and Warehouse Process etc. The PackageAudit table contains all the necessary columns from the PackageConfig table that need to be audited as well a column for the user who made the change and the time of the change. This table has a trigger that will add a new row to the audit table whenever the main table is added to or changed. The PackageConfigLog will contain the necessary columns from the PackageConfig table that will allow the administrator to see which configuration values were used during each execution. Trust me, without this table you can be testing and get unusual results, take many hours hunting down what you might have done wrong and in the end find out that one of you colleagues change a config value that was vital to your test. If you had checked this table first, life could have been a lot less painful.
SSIS
Inside you package you will need a task to overwrite the variable values as defined in the package. You may want to write a custom task for this or just use a script task. As this is a task that will be used in all you packages I would suggest using the custom task options as changes to the script once it has been implemented in 100s of packages can be a real nightmare.
I have included a simplified version of the code below
' open connection
oSQLConn = New SqlConnection(strConnectionString) : oSQLConn.Open()
oSQLComm = New SqlCommand("uspGetConfigInfo", oSQLConn)
oSQLComm.CommandType = CommandType.StoredProcedure
' Add parameters to SQL command
Call ParametersGetConfig()
' Execute and return dataset
oSQLReader = oSQLComm.ExecuteReader()
' Loop through reader
While oSQLReader.Read
If Dts.VariableDispenser.Contains(oSQLReader.GetString(0).ToString) Then
' Lock variable for write
Dts.VariableDispenser.LockOneForWrite(oSQLReader.GetString(0).ToString, oVariables)
' Check variable is the correct type
If oVariables.Item(oSQLReader.GetString(0).ToString).DataType.ToString = oSQLReader.GetString(2) Then
oVariables.Item(oSQLReader.GetString(0).ToString).Value = Convert.ChangeType(oSQLReader.GetValue(1), _
oVariables.Item(oSQLReader.GetString(0).ToString).DataType)
' Report information
Dts.Events.FireInformation(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " has been updated.", _
String.Empty, 0, True)
Else ' Report incorrect data type
Dts.Events.FireWarning(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " has a different data type then " & _
"that defined in the config database.", String.Empty, 0)
End If
Else ' Report missing variable
Dts.Events.FireWarning(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " is missing.", String.Empty, 0)
End If
End While
' Close SQL Data Reader
oSQLReader.Close()
A stored procedure (uspGetConfigInfo) will get the variable information from the table discussed earlier and find the relevant variables, verify that the variable type are correct and then replace the value in the package. The change will be reported to the package through the component event namespace and can be captured by your chosen logging mechanism in the OnInformation event handler. The config log information could be implemented using a similar process in which the valid variables (with the correct data type) are written away to the PackageConfigLog table.
Encryption:
Using SQL Server encryption you can encrypt variable values in your PackageConfig table and decrypt them in your GetConfigValues stored procedure. Ensure that these values are not written to the package config log as that would obviously ruin the whole purpose of the encryption process.
