Package Configurations
Package configurations allow you to configuration information outside of SSIS, and to have that information affect SSIS package properties at run time.
Imagine being able to create a central repository for connection manager definitions that can be used by any number of independent packages. Should a server that is used by these packages fail, and have to be replaced by a backup server; imagine being able to change the connections string within this central repository and have the change immediately picked up by every impacted SSIS package. I am going to demonstrate how package configurations will allow you to do exactly that.
Creating the Repository
As the connection manager repository will be used by any number of unrelated packages, I would strongly recommend housing it in its own database. I have created a new database named ConnectionControl.
There are two ways to create the repository table; either manually, as demonstrated below, or through the 'Package Configuration Wizard', which I will introduce later. Create a table with the following structure to store your connection configurations:
CREATE TABLE [dbo].[SSIS_Connection_Configurations]
(
ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL
)
Creating a connection
Now we need to create a connection manager to point at the ConnectionControl database in the standard way:
- Right click within the 'Connection Managers' pane and select 'New OLE DB Connection...'
- On the dialog click 'New...'
- On the 'Connection Manager' wizard populate the server name and database fields to point at your 'ConnectionControl' database and click 'OK'
Complete the process by clicking 'OK' again.
Repeat the above process to create a second connection manager, this time pointing at a database that you would like to store in the repository.
Rename both connection managers to remove the name of the server, and preferably use a standardised naming convention. Be aware that whatever name you give them is the same name that you will need to use whenever reusing the connection in other packages.
Adding the connection to the configuration table
To accomplish this we will need to set up a package configuration. From the 'SSIS' menu choose 'Package Configurations...'
Ensure that the 'Enable Package Configurations' check box is ticked and click on the 'Add...' button.
Complete the 'Package Configuration Wizard' as shown below.
Note: The 'New...' button next to the 'Configuration table' drop down list can be used to create the repository table.
The Configuration Filter field is free text, and should be completed with the name against which you would like to store the connection configuration data in the repository.
Click 'Next' and you will be presented with a second page which allows you to specify the property that you would like to store, in this case the connections string.
After navigating to the correct connection string click 'Next'. Provide a name for the newly created configuration and click 'Finish'.
It is now possible to view the stored configuration data by querying the SSIS_Connection_Configurations table.
ConfigurationFilter | AdventureWorks2008 | |
ConfiguredValue | Data Source=JON_LAPTOP\SQLSERVER2008; Initial Catalog=AdventureWorks2008; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False; | |
PackagePath | \Package.Connections[AdventureWorks2008].Properties[ConnectionString] | |
ConfiguredValueType | String |
Reusing Connections
So, you now have the definition of a connection to the AdventureWorks2008 database stored in your repository. The next stage is to reuse that connection in a new package.
Create a new package and create 2 OLE DB connection managers. The first connection manager should connect to the repository, ConnectionControl. The second connection manager should have the same name as the stored connection, AdventureWorks2008, although it is unimportant as to where the connection points, as the required connection configuration will be taken from the repository.
Launch the 'Package Configuration Organizer' from the SSIS menu, and click 'Add...'. Complete the wizard as below:
This time the name of the 'Configuration Filter' can be selected from the drop down list. This time when you click 'Next >' you will receive a message box asking whether you would like to reuse the stored configurations or overwrite them.
Click 'Reuse Existing', name the configuration, and click 'Finish'. And that is it!
Should you have a need to change the stored connection configuration you have 2 options. Either:
- Modify the package configuration within one of your packages to point at a new server, and click 'Overwrite'. Or,
- Modify the connection string directly in the repository.
Either way, the next time any of the affected packages run they will pick up the changes and run against the emended server.
Next Up...
In the next post in this series I will describe how to maintain two connection repositories; one for your development environment and one for your production environment. I will show you how to configure your packages so that the particular repository your package uses will be determined automatically depending on where the package is deployed. In this way packages can be moved from a development to a production environment without the requirement to reconfigure the package.