Sorry it took me so long to reply. I spent about an hour putting together a reply, only to receive an error when I clicked post, and not being able to go "back" to it in my browser history.
Here's the recreation of the post:
Thanks for the kind words. I definitely enjoyed doing the presentation, and am glad it was timely. You should make sure you attend next month's meeting, as I believe Steve is going to be talking about package frameworks exclusively.
As for how we're grouping our configurations, we have the one DB: SSISConfig, and then inside we roughly group the configurations into tables, with 1 table per package. Then within the packages/tables we're breaking things down into logical groupings using the ConfigurationFilter column in the table (I'll show examples in a second), which corresponds to the Configuration String which you can see here, from the Package Configuration dialog in the package:
This is a screen capture from the package I talked about in the presenatation: the MasterPackage, which controls the execution of all the packages we execute in our ETL process.
Here's some data from that table, which is SSISConfig.dbo.MasterPackage. It's got the column headers in the first row, followed by comma separated values in the subsequent rows:
-
ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType, DefaultConfiguredValue
-
ConnectionStrings, <connection string stuff>, \Package.Connections[Ticketing DataMart].Properties[ConnectionString], String, <connection string stuff>
-
ConnectionStrings,<connection string stuff>, \Package.Connections[Foundation Datawarehouse ADO.net].Properties[ConnectionString], String, <connection string stuff>
-
DisableFlags, False, \Package.Variables[User::DisableTicketing].Properties[Value], Boolean, False
-
DisableFlags, False, \Package.Variables[User::DisableMergeProcess].Properties[Value], Boolean, False
-
DisableFlags, False \Package.Variables[User::DisableHouseholding].Properties[Value], Boolean, False
It's not the best example of how we utilize the configurations, but it's still useful. The entries I've chosen to show can be used to change the settings of the various connection strings the package uses, and to enable and disable entire steps of the ETL quite easily. Trust me, this has been very useful on a regular basis. You may also notice that we don't have the TruncateConfiguredValue column in this table. That's because we don't have any special settings in this table for when we want to Truncate the values in our data warehouse.
Here's another example using one of the individual dimensions in our data warehouse, which is where we do a lot more utilization of the Default and Truncate columns. This is from SSISConfig.dbo.DimCustomer, which controls the settings for the conformed Customer package/dimension that I talked so much about in the presentation:
-
ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType, DefaultConfiguredValue, TruncateConfiguredValue
-
ConnectionStrings, E:\SSIS\ETL\FDW\Test\Customer\, \Package.Variables[User::OutputFilePath].Properties[Value], String, E:\SSIS\ETL\FDW\Test\Customer\, E:\SSIS\ETL\FDW\Test\Customer\
-
ConnectionStrings,<connection string stuff> \Package.Connections[Ticketing Transaction Source System - OLEDB].Properties[ConnectionString], String,<connection string stuff>,<connection string stuff>
-
RawFile, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw, \Package.Variables[User::RawFileLocation].Properties[Value], String, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw, E:\SSIS\ETL\FDW\Test\Customer\Checksum.raw
-
Truncate, True, \Package\Truncate DimCustomer.Properties[Disable], Boolean, True, False
Here you can see a couple more ways that we leverage the SSIS package configurations. Not only are we still using the Connection Strings like we were in the MasterPackage: to control which DBs and servers the package was connecting to. We're also using it to control where the package is physically located on the server itself. This way we can have multiple versions of packages residing on the same server. This is great for running both Test and Production on the same server, for example. The last line also shows how we can control the execution of an individual package using the Disable property. I have to make a quick caveat here. Some people feel this is not a best practice, and have run into problems with it. It's worked well for us, but if you wanted to do things differently, such as putting a variable into your package, then checking that variable in your step, most commonly in an Execute SQL Task or an OLE DB Source, go for it. That last entry enables or disables an Execute SQL Task in the package which contains TRUNCATE dbo.DimCustomer. If it's enabled, it truncates, if it's disabled it doesn't get executed. Simple. Handy. We then use the Default and Truncate columns from the stored procedures we've built for controlling the configurations.
Last thing for this post. This is not a concept we came up with ourselves. We took an existing concept, the 2 pass configuration using SQL Server Tables for storage of the configurations, and added a simple idea, which is storing the typical configurations with the current configurations. This means that the majority of information on how to set this all up is contained both in BOL and on the web. Just take those tutorials and add a couple of columns, and your'e off and running. Here's a good article, for example:
http://www.mssqltips.com/tip.asp?tip=1405
Hope this helps, and post any other questions you have,
Rick