So, there's been a lot of discussion lately on the newsgroups about package configurations. Package configurations are new in SSIS. They're a way to make changes to your packages without actually editing the packages. They are partially targeted at filling the hole left by the departure of the dynamic properties task, which was deprecated because it simply could not continue to function in the new architecture of Integration Services.
Package configurations come in a few different flavors including, SQL, INI, XML, Registry, Environment variables and parent package. Here's how they generally work. You create a configuration, usually in the package configuration organizer wizard. Select some properties you want configured and click OK. You get there by right clicking on the designer surface and selecting Package Configurations.
Once you've opened the wizard you can enable/disable package configurations by clicking on the “Enable package configurations” checkbox. This checkbox enables them for the current package only and doesn't effect any other packages, even those in the same solution or project. It's important to understand, also, that all package configurations, with one exception, are applied at load time. So after the package is completely loaded, configurations are applied, not at package execution time. The one exception is Parent Package Configurations. Those configurations are actually applied when the Execute Package task is executed because that's when it's most likely that the parent package variable that's being referenced in the configuration is valid.
Some things everyone should understand about package configurations:
- More than one can be applied to a package.
- One configuration may be applied to more than one package.
- Each package configuration is applied in the order shown in the configuration wizard, except Parent Package Configurations.
- If an entry in a configuration fails, it will only emit a warning. Configurations should never raise an error or cause a package load to fail.
- The location of a configuration may be hard coded in the package, or pointed to from an environment variable. Which may cause you some grief when you deploy packages with configurations. Use indirect configurations (Environment variable contains location of configuration) for best results here.
- Using one configuration file per server is optimal. That's a whole article by itself.
Using package configurations and property expressions, most properties on most tasks may be configured at load time. The exception is pipeline transform and adapter properties. Pipeline property expressions didn't make it in and likely won't in this ship cycle.
Package paths - What's a package path. It's essentially a URN that points to a property on an object in the package. Here are some examples:
\Package.Connections[{E9598474-461E-48F7-B902-52A140B7FE14}].ConnectionString
\Package\LoopOverFiles\sql-TruncateLoadTable.SqlStatementSource
\Package.Variables[::FileLocation].Value
They all start with \Package. The '.' character indicates that a property follows. The '\' character indicates that a container or task follows. The string between the '[' and ']' characters indicate an index into a collection. That index will either be the name of the object or the ID. The ID is safer to use because, unless someone manually edits the package, the ID will never change. The name is more portable because more than one package may have the same named connection for example. That way, you may use the same configuration for multiple packages. For example, a configuration with the third package path above could be applied to all packages that have a variable named FileLocation in the package's variable collection (defined at package scope).
Now, when configurations are applied to a package, IS reads the configuration, finds the property the package path points to, and sets it to the value specified in the “ConfiguredValue“ section of the configuration. One thing you should be aware of though. Once the package is configured, it's just the same as if you had edited the values of each of the properties yourself in the designer. If you save the package after the configuration has been applied, the configured properties will be saved with the new values. It's not necessarily bad or good, it's just good to be aware of it. This is one of the reasons why the enable configurations setting exists, so you can turn off configurations as you're developing your package and avoid such unexpected behavior.
The two types of package configurations that haven't been documented yet in books online is INI and SQL configurations. INI package configurations have basically been provided for backward compatibility. Although, the INI files you used for the dynamic property task are not compatible, it may be a little easier for you to convert them to use INI configurations. I like INI configurations because they're simple and clean. In spite of what some say, and I know I'm going to get flamed for this, but XML isn't for everyone for everything... OK, flame away. :)
SQL Package Configurations are likely the most important because they provide the possibility of a central configuration store for your entire enterprise. Support for SQL package configurations is late, but continuing to improve. IDW11 CTP has incomplete support for SQL Configurations. We're working on this feature and it should get easier to setup in beta 3. If you'd like to start using them now, you'll need to create a table in one of your databases. Here's the script to do setup:
USE ISConfigurations
CREATE TABLE [dbo].[SQLConfigurations]
(
[ConfiguredValue] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfigurationType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PackagePath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfigurationFilter] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
INSERT INTO SQLConfigurations VALUES
(
'C:\temp\mytest.txt', --Configured Value
' ', --Configured Type, no longer need
'\Package.Connections[{EAA98474-461F-48F7-B902-COFFEEBEEF42}].ConnectionString', --Package Path
'String', --Configured Value Type
'' ) --Configuration Filter
UPDATE: See related blog here
SQL Configurations use a connection manager to access the table where the configurations are stored and you may create the table in any of your databases. It's not limited to say, MSDB for example.
You can find more about configurations in books online here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/91ac0347-f908-44f5-bd3d-115790223af4.htm
This is just a quick runthrough. Hope this helps clarify package configurations a bit. Give them a try. Give us some feedback!
Maybe I've just caused you to think of more questions. Let me know.
Thanks,
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden