There are 3 different ways you can create configurations:
• Split configurations by package name
• Split configurations by connection name
• Split configurations uniquely by data source name (i.e. Database name or filename)
The first two approaches are the most time consuming to maintain because you have to change several places when connection settings change. Creating a shared configuration is the most effective way of reusing configurations. Imagine you have 100 packages and each package has 10 connections. In that case you end up maintaining 1000 configuration entries if you go with option-1 or option-2 which is not only time consuming but error prone as well.
When you create shared configurations (option-3) you end up maintaining fewer configuration entries based on database or file name which produces a lot less configuration entries.
The only drawback of shared configurations is that you have to name the connections the same way in all packages because configuration refers connection by name so if one package names its connection "adventureworks_connection" and the other package "connection_adventureworks" then it will not work even though both have the exact same ConnectionString.
1. Launch Secure Configuration Wizard for the single or multiple packages ([Ctrl] + [Left Button Click] for individual item selection or [Shift] + [Left Button Click] for range selection).
2. Click on "Add to all" to create shared configurations for all selected packages otherwise click "Add to selected" to modify a single package.
3. On the configuration type screen select configuration type (XML or SQL) and enter location settings.
4. Check split configuration option, select "by connection name" from the drop down and check "Generate shared configurations" option
5. Click on the "Smart Rename Options" button to review the settings.
6. On Smart Rename options you can select the naming convention for connections (and configurations). You can see a preview of connections before and after the rename.
7. On the property selection screen you can either check desired Properties you want to configure for each Connection (Mostly ConnectionString) or you can change to "Simple Mode" that will hide properties and create a compact list for only ConnectionString property. You can also pick desired properties to show up in the list using property filter option (Only available in Advanced Mode) that way you can use the check all feature by clicking Root node. Click "Next".
8. Review selection and click "Start". This will process the selected connections and create Configuration entries in Database or it will create XML Configuration files depending what type of configuration you have selected. You will also notice that connection names in all selected packages are standardized to match with smart rename naming convention.