A question was asked in a recent class about whether a single configuration table could be used to hold settings for a package that is copied to multiple servers. In the particular example we discussed in class, the package executes on these separate servers and also happens to extract from and/or load to databases on the same servers. The student asking the question indicated there were many servers in the work environment hosting identical databases containing different data and was curious if the process of maintaining configurations could be simplified by using one table. I have seen several ways to approach configuration table management in blogs and forums, but for the benefit of my class, I'm including the following notes as a more polished version of the specific discussion we had.
The Situation
There are three database servers, each of which host a common database (i.e. same name, structure, different data). For this example, let's identify these servers as Server_A, Server_B, and Server_C. The database on each of these servers is MyDatabase. A separate database server, Server_ITMgmt, hosts a configuration table to store settings for any server on which SSIS packages execute.
The final configuration is shown in the following illustration. You can read the details about how we implemented the design in class below.
The Objective
Execute MyPackage to extract data from MyDatabase on each of the three servers. During development of the package, you define a connection to a single server; let's assume Server_A for this example. However, you can easily use this same package to connect to databases on other servers by using a package configuration to change the server named in the connection string at runtime. Even though the same package is subsequently deployed to separate servers, all configuration settings for each server are defined in a single table. Let's say this table is MyConfigurations in the MySSISConfigurations database on Server_ITMgmt.
Beyond the placement of the package on all servers and the need to connect to a different server at runtime, the purpose of this package isn't particularly relevant. If I define a specific scenario, there are likely to be other ways to tackle the problem while the goal of this discussion is to focus on one approach to using package configurations. That's my story and I'm sticking to it.
The Steps - An Overview
First, let's review the steps to perform at a high level so you can see where we're heading and have the main steps available as a quick reference. In the next section, I'll break down each step in greater detail.
-
Create environment variables on each server to identify the location of the XML configuration file and the filter used to locate values in the configuration table.
-
Add a Connection Manager to define the connection string to the configuration database.
-
Create an XML configuration file (.dtsConfig) to define a direct connection to the configuration table.
-
Copy the XML configuration file to the other servers and then edit the file to update the server name in the connection string.
-
Create a direct SQL Server configuration to connect to the configuration database.
-
Replace the direct configurations with indirect configurations.
-
Populate the configuration table with values for the remaining servers.
-
Test the package configurations by executing the package on each server.
The Steps - In Detail
1. 1. Create environment variables on each server to identify the location of the XML configuration file and the filter used to locate values in the configuration table.
Environment variables are useful when you plan to execute the same package on more than one server and need a common location to store information for package configurations. On each server, you can create system environment variables as shown below.
Server A
PKG_CONFIG_LOCATION
C:\ETL\ConfigurationConnection.dtsConfig
PKG_CONFIG_FILTER
"Server_ITMgmt.MySSISConfigurations";"[dbo].[MyConfigurations]";"Server_A";
Server B
PKG_CONFIG_LOCATION
C:\ETL\ConfigurationConnection.dtsConfig
PKG_CONFIG_FILTER
"Server_ITMgmt.MySSISConfigurations";"[dbo].[MyConfigurations]";"Server_B";
Server C
PKG_CONFIG_LOCATION
C:\ETL\ConfigurationConnection.dtsConfig
PKG_CONFIG_FILTER
"Server_ITMgmt.MySSISConfigurations";"[dbo].[MyConfigurations]";"Server_C";
As you'll see in later steps, the value of PKG_CONFIG_LOCATION identifies the dtsConfig file, an XML configuration file containing the connection string to the MySSISConfigurations database. It doesn't matter whether the name of the dtsConfig file on each server is the same or different or whether they are stored in a consistent location, although it's easier to manage if you maintain some sort of consistency. The key here is the difference in the file contents which you'll define in step 3.
2. Add a Connection Manager to define the connection string to the configuration database
-
Open the package in Business Intelligence Development Studio.
-
In the Connection Managers tray at the bottom of the package designer, right-click and then click New OLE DB Connection on the context menu.
-
In the Configure OLE DB Connection Manager dialog box, select an existing data connection if you have one already for the database to host the configuration table, or click New to create a new data connection. If you create a new data connection, you'll need to specify the server name, authentication method, and database. Once created, you can select the new data connection. Click OK to close the dialog box.
The connection manager appears in the format ServerName.Database name in the Connection Managers tray. You might consider renaming the connection manager to make it more generic for use across many servers. I named mine Configuration.
-
Save the package, but keep the package designer open.
3. Create an XML configuration to define a direct configuration to the configuration table.
A two-part approach to package configurations is recommended to accommodate relocation of the configuration table in the future: an XML configuration and a SQL Server configuration. The XML configuration file contains the connection string to the table in a SQL Server database. The table, in turn, contains connection strings to use for the multiple servers. You can't very well have the package configuration connect to the table without having defined the connection string first. Also, if you move the table to another location, it's very easy to modify the dtsConfig file using a text editor.
-
On the SSIS menu, click Package Configurations.
-
Select the Enable Package Configurations check box, and then click Add.
-
On the Welcome page of the Package Configuration Wizard, click Next (unless you have already disabled this page, in which case you can skip this step).
-
On the Select Configuration Type page of the Package Configuration Wizard, keep the default selection for Configuration Type: XML Configuration File.
-
Type a name and location for the file (consistent with the values used for the environment variable PKG_CONFIG_LOCATION in the previous step) in the Configuration File Name box: C:\etl\ConfigurationConnection.dtsConfig, and then click Next. At this point, the file doesn't exist, but the wizard will create it. We will eventually change the configuration to look at the environment variable to find the configuration file. Optionally, you can replace the default name, Configuration 1, with an alternate name, such as Configuration Table Connection.
-
Click Finish, and leave the Package Configurations Organizer open.
4. Copy the XML configuration file to the other servers.
Using your preferred method, copy the ConfigurationConnection.dtsConfig file from the C:\etl folder on Server_A to the same folder on Server_B and Server_C.
5. Create an direct SQL Server configuration to connect to the configuration database.
This step builds the table and populates with initial values, but you'll replace this configuration with an indirect configuration in the next step. It's just much easier to let the wizard do the bulk of the work for you.
-
Switch to Business Intelligence Development Studio, and then, in the Package Configurations Organizer, click Open. (If you closed the dialog box, you can access it again on the SSIS menu by clicking Package Configurations.)
-
Click Add, and click Next if you see the Welcome page of the wizard.
-
On the Select Configuration Type page of the wizard, in the Configuration Type drop-down list, select SQL Server.
-
In the Connection drop-down list, select the connection manager you created in Step 2.
-
To the right of the Configuration Table drop-down list, click New.
-
Optionally, change the name of the table. For my scenario, I used MyConfigurations. Click OK to execute the script creating the table.
-
In the Configuration Filter drop-down list, type Server_A.
You can use any string for a configuration filter. When the package executes using this configuration, all rows with this string will be selected from the configuration table, and the configuration settings will be applied to the package. If the table already exists, you can select any existing filter in the drop-down list.
-
Click Next.
-
On the Select Properties To Export page of the wizard, scroll (up, usually) to locate the Connection Managers folder, expand the connection manager for the database on Server_A from which your package extracts data, expand the Properties folder, select the ConnectionString check box, and then click Next.
-
Optionally, you can replace the default name, Configuration 1, with an alternate name, such as Source Database Connection.
-
Click Finish, and leave the Package Configurations Organizer open.
At this point, you have two configurations. The XML configuration points directly to a physical location on the file system and the SQL Server configuration points directly to a table in a specific SQL Server database. By creating the direct connections first, you have a dtsConfig file and a configuration table that are properly structured. Next, you'll replace the direct configurations with indirect configurations that use the environment variable values to locate the values for the two configurations.
Hint: Take a good look at the Configuration String for the SQL Server configuration type. You'll need to use a similarly constructed string in the environment variable for the indirect SQL Server configuration.
6. Replace the direct configurations with indirect configurations.
-
In the Package Configurations Organizer, select the XML configuration, and then click Edit.
-
Click Next to bypass the Welcome page if necessary, and then, on the Select Configuration Type page, select the Configuration Location Is Stored In An Environment Variable radio button.
-
In the Environment Variable drop-down list, select PKG_CONNECTION_LOCATION, click Next, and then click Finish.
-
Select the SQL Server configuration, and then click Edit.
-
Click Next to bypass the Welcome page if necessary, and then, on the Select Configuration Type page, select the Configuration Location Is Stored In An Environment Variable radio button.
-
In the Environment Variable drop-down list, select PKG_CONFIG_FILTER, click Next, and then click Finish.
7. Populate the configuration table with values for the remaining servers.
At this point, the configuration table contains values only for Server_A based on the selection of the ConnectionString property when creating the direct configuration. Now you'll add rows for Server_B and Server_C.
Since the table is small and since I have only two rows to add, I am going to do the horror of horrors and manually key in values. To be more precise, since I'm loathe to type more than I must, I actually copied the first row and pasted into a new row, changing values in the ConfigurationFilter and ConfiguredValue columns to point to the proper server. If your conscience moves you to do otherwise, set up a script to insert the necessary values.
Using SQL Server Management Studio (or your favorite method), insert values into the configuration table, so the first two columns in the table looks similar to the image bloew and the third column contains \PackageConnections[Source_DB].Properties[ConnectionString] (space doesn't permit a full image - sorry):
8. Test the package configurations by executing the package on each server.
Just as there are many ways to configure a package to use different values at runtime, there are many ways to execute a package. For this example, I'll assume that each package is copied to the file system of each server. The simplest way to execute a package on the file system is to use the DTEXECUI utility.
-
Click Start, click Run, type dtexecui in the Run dialog box, and then click OK.
-
In the Execute Package Utility dialog box, in the Package Source drop-down list, select File System.
-
Click the ellipsis button to the right of the Package box, navigate to your package file, and double-click to select it.
-
Click Execute.
Note: If you don't configure the environment variables, the package will run without error or warning using the properties defined in the package. If the package doesn't produce the results you expect from the revised configuration, try the following steps:
-
Double-check the values in the XML file.
- Make sure the environment variables are configured properly.
-
Verify the values in each column of the configuration table are correct.
-
Open the package on the alternate server using Business Intelligence Development Studio and check the configuration settings.
Summary
With Integration Services, you can devise many different solutions for modifying the behavior of a package at runtime. There are other configuration types you could use, such as registry entries or parent package variables. You can refer to different configuration files at runtime or even set different properties when using the execution utilities. Of course, you could also separate packages from the database servers and use a single SSIS server to execute one package against multiple servers. The point of our discussions was not to explore all possible ways to design a package for reuse, but specifically to build one configuration table to be used by all servers running SSIS packages. Specifically, one environment variable identifies the location of the XML file pointing to the configuration table and another environment variable provides the information needed to select the applicable rows from that configuration table. Thus, one possible approach is to copy a package from server to server and then use the server-specific settings to modify the package behavior at run-time. Package configurations provide endless flexibility for modifying package execution.
