Best practices suggest creating a package template with the Package Configurations already predefined for your project, and then copying and the template package as a starting point for each additional package in your solution. This way you won’t have to add the configurations to each package, but only do it once for your solution and copy the configurations by using a copy of the template for each new package in your solution. Here is one way to to this.
1. ADD AN ENVIRONMENT VARIABLE TO THE SERVER TO POINT TO THE CONFIGURATION DATABASE
On the server that will be running the SSIS packages, add an Environment Variable called “SSIS_CONFIG_DB” to hold the value of connection string for the Configuration database that will store all the other connection strings.
All Programs – Computer – right click on Computer and select Properties
Select Advanced System settings
On the Advanced tab, click on the Environment Variables button
Add a new System Variable by clicking the New button and filling the Variable name SSIS_CONFIG_DB and the variable value in the format of:
Provider=SQLNCLI10.1;Data Source=servername;Integrated Security=SSPI;Initial Catalog=databasename
2. (CLOSE AND) OPEN BIDS
Open BIDS solution AFTER the variable is created, so that your variable will show up in the dropdown.
3. REMOVE ANY SHARED DATA SOURCES IN YOUR PROJECT SOLUTION
Open or create a new solution. If you are using Shared Data Sources, delete them from the Data Sources folder in the Solution Explorer now. Your packages will retain their individual connection managers. Shared Data Sources and Package Configurations don’t mix very well, and here’s a good write up on why.
http://msdn.microsoft.com/en-us/library/cc671619.aspx
4. CREATE A NEW PACKAGE
Add a new package to your solution called Package_Template.
5. ADD A CONNECTION TO YOUR PACKAGE FOR THE CONFIGURATION DATABASE
Add a Connection to your package which connects to the database you have chosen to hold all your configuration strings in Step 1. To do this, right click in the Connection Managers pane in your Package_Template package and set up the appropriate connection.
6. ADD THE ENVIRONMENT VARIABLE CONFIGURATION TO YOUR PACKAGE
This Configuration needs to be first in the list of Configurations defined in your package, since the other Configurations will be referencing it.
Right click on Control Flow, select Package Configuration. Check the Enable package configurations checkbox. Then click the Add button, select Configuration Type “Environment Variable” and select your SSIS_CONFIG environment variable from the dropdown.
Click Next. Select the Target Property by navigating to the Connection Manager for the Configuration Database which you set up in step 5, and selecting the ConnectionString property.
Click Next and give your Configuration a name “ConfigConn”
7. ADD A SQL SERVER CONFIGURATION TO YOUR PACKAGE
Add another configuration of type “SQL Server” by clicking the Add button. Select SQL Server from the Configuration Type drop down. Select the connection to your DW (the one you are pointing to in your environment variable) and if this is the first SQL configuration you are adding to this database, click New to create the table in the DW where the connection strings will be stored. Or, if this is not the first time, then select the name of your configuration table from the drop down [dbo].[SSIS Configurations]”
If this is the first time you are creating an entry for this connection, create a Configuration filter by typing in an appropriate name for your connection string, which will identify that connection in the table. Otherwise select the name of your existing entry from the dropdown.
If this is the first time you are creating a Configuration to this database, you will need to navigate to the appropriate Connection Manager and select the Connection String property.
If this is not the first time you have used this particular Configuration filter a message box will ask you if you want to reuse the existing configuration or overwrite. Select Reuse Existing, so the information in the database about this connection will not be overwritten.
Give your connection Configuration a name. Example “NAVConn”
You can go ahead and add the rest of your package connections as SQL Server configurations. Take a look at the table “SSIS Configurations” in the Configuration database you defined, to see the connection strings being entered. When you update the connection string in this table, your packages will point to the updated database. Try it by updating the value and reopening your package.
If you want to add logging to your package template at this point you can look at this post – Configure SSIS Logs
Thanks to these two sites for giving me some tips on this subject:
http://www.rafael-salas.com/2007/01/ssis-package-configurations-using-sql.html