Using SSIS SQL Server Configurations with Indirectly Configured Connection String

Overview:

Here is a quick article on using SSIS Configurations that I wrote awhile back.  It contains notes on how to set up an indirect configuration to the SQL Server where the configuration table is stored.  Finally, it shows how a user would add configurations to the SSIS Package via the SQL Server Configurations. 

Notes:

We have set up an environment variable "SSISConfig" which contains the connection string to the configuration table. This config string will be used to populate a connectionmanager’s connectionstring so that we can then connect to the config table. Afterwards we can connect to the config table where we can set up the rest of the configuration elements as seen below.  NOTE: You can (and should) limit the configurations to hold only logically grouped items for each configuration filter.  i.e., you would have only a single connection string for that servers configuration filter. 

 

ssis_package_config_4

Instructions:

Adding the Environment Variable:

  1. Hit the start menu and go to settings -> control panel
  2. click on system
  3. click the advanced tab
  4. click the environment variables button
  5. click the new button underneath system variables
  6. variable name: SSISConfig
  7. variable value: ? … type in the connection string to the database containing the config table, ex: Data Source=FLSQL2K5TEST;Initial Catalog=SSISConfigurations;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;User ID=;Password=;
  8. Restart your system

Adding the Configuration Connection String (via system variable):

  1. Open your package in BIDS
  2. Click the SSIS menu -> Package Configurations
  3. Click Add
  4. Configuration Type: Environment Variable
  5. Environment Variable: SSISConfig
  6. Click Next
  7. Find the configuration connection manager and click on the connection string property
  8. Click Next
  9. Choose a Configuration Name and hit finish

ssis_package_config_2

Adding the SQL Server Configurations:

  1. Open your package in BIDS
  2. Click the SSIS menu -> Package Configurations
  3. Click Add
  4. Configuration Type: SQL Server
  5. Connection: Configuration (or whatever you named your connection manager to connect to the configuration table)
  6. Configuration Table: SSISConfigurations (or the table name you have named your table, although this will be the default)
  7. Configuration Filter: MyConfiguration (this name will determine which variables are associated with this configuration set and will be available from either the drop down list or to type in if you would like a new name, i.e. if you call this genericconnectionstrings and you would like to use the same connection strings in multiple places you would put the same filter in each, but if you want to create a new set and it pops up asking if you would like to use the exsisting set you will need to hit cancel and find another filter name)
  8. click next
  9. Add all of the properties which you would like to be saved in your configuration settings, these can be connection strings, variable values, etc…
  10. click next
  11. Add a configuration name and hit finish

ssis_package_config_3

 

 

Advertisements
This entry was posted in SSIS and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s