SSIS 2005, 2008 and 2008 R2 allow using Package Configuration with the following options:
- Environment Variable
- Often used to provide connection string to sql server table or XML file
- XML Configuration File
- Used to provide configuration values
- Registry Entry
- Rarely used
- Parent Package Configuration
- Used to set variable using parent value
- SQL Server
- Used to receive configuration from sql server table.
Set ups I came across are:
- Environment variable + XML file
- Environment Variable + SQL Server
- Environment Variable + SQL Server + Parent Package Configuration for child packages
The problem I found with above set ups is that it is not easy to get only configuration for specific package. For instance SQL Server configuration is able to filter configuration but it expects you use all available values which is often not what you want when you use multiple packages. This results in setting up more variable than you want and making the package less readable. If you don’t set up variables in individual packages this will work however you may get warnings and you have to deal with warning you will have to ignore which makes log reading harder.
Another common problem is that sometimes a variable is not set at run time for many different reasons which is a serious problem as package will use configuration embedded into the package which means it might use development connection string for production run which will either fail the package or make it run without actually raising a problem.
From SQL Server 2012 we will have new way of configuration which should resolve many of the issues (and most likely introduce some new ones). But if you use 2005, 2008 or 2008 R2 you can still try a different approach.
The approach I use collects variables from a package, extracts the variable value from SSIS Configuration table and sets the variable in the package.
NOTE: Ensure that you can use this approach based on your specific requirements.
The advantage of this approach is that I ensure that all variables are set correctly and if not it fails the package which is actually what I want as incorrect variable value has serious consequences.
It also allows me pick individual variables from a group of variables without any unwanted warnings.
Below I will show you how to create a template package that will contains all the necessary logic to do it and which you can simply copy/paste for new packages.
I added Environment Variable called SSISConfig to my computer which contains connection string to my SSISConfig database.
NOTE: Restart is required for system environment variables and not for user environment variable, however for user environemnt variable you will most likely have to restart the problem you use (for instance BIDS, SQL Agent, command tool etc)
I created new package; added new variable conSQLConfig that will hold connection string from environment variable and I added Environment variable to package configuration to set the variable.
NOTE: Below you can see blue triangle next to the variable that is because I use BIDS Helper which means that variable is set using package configuration.
Now I will add connection manager and set connection string using conSQLConfig variable that is set using environment variable and this connection will be used to extract the remaining configuration.
In order to make dynamic configuration work I need several new variables. See below my setup.
dynvarPackageFilterName – contains group of variable I want to extract.
dynvarPackageVariables – will contains all variables I want to set at run time from config table.
dynvarPackageVariableName – is individual variable which I will use during for each loop.
dynvarPackageVariableValue – is the value I will extract from config table for a specific variable.
Next I will add variable I want to set at run time. I will use gconfig prefix that will separate variables that are set at run time from those that are set somewhere inside the package or using another method.
My variable name is gconfigConOLE_Staging and it will hold connection string to staging database.
Next let’s add script item that we will use to read all variables.
I use read/write variables option because it is easier to use one than both option and I will select ALL variable so I will make fewer mistakes later on when I need to add new variable or rename variables.
Below is script that will add check all variables that we selected in previous step and it will add variables starting with gconfig to an array called dynvarPackageVariables which we will use later on.
Next we need to add for each loop that will read our array (dynvarPackageVariables) that holds all variables with gconfig prefix and execute code inside for each variable.
In variable mappings we map our array value (variable name) to dynvarPackageVariableName that we will use inside the for each loop container.
Next we will add sql task to our for each loop and execute stored procedure cfg.GetValueForVariable that will take two parameters: FilterName and VariableName and … (see next paragraph)
And … will return VariableValue for the variable name we specified
Next we need to add script component to our for each loop after sql task and as before sell all variables in ReadWriteVariables section
In the script itself we will use several lines of code to set the variable value for the currently executing variable name in the for each loop.
Now we will configure new connection conOLE_Staging and use our gconfigConOLE_Staging variable to the ConnectionString property.
For demonstration purposes I added new value gConfigTest with value in desing (BIDS) set to DesignValue and in Config table the value is ConfigTableValue. As you can see in “watch” window the value changed to ConfigTableValue
What is missing from this blog post?
Config table script and stored procedure code is missing. Once I test this approach more I will upload it and upload the actual package.
Variables are ment to be set to empty at design time and certain tasks need to be set to DelayValidation = True. I will try to update the blog post soon. See below advantages for screenshots of empty variables at design time.
Advantages of this approach:
- “Global” variables are easily added to the package by selecting all variables in two script tasks.
- If the variable does not exist in config table the package will fail (intended behaviour = validation)
- It is part of your template and no extra work is required. Simply select variables in two script components.
- I’m not aware of any side affects
- Variables are EMPTY at design time after the pacakage is developed and ready for testing (see below). Which means you cannot forget to set variables which saves a lot of frustrations and time during unit testing, deployments (SysTest) and debugging.
Limitation or extract steps of this approach:
- Currently it only works with one set of group of variables. I will try to change it so it works with multiple groups for instance "DW General" and "Process specific"
- Components are embedded into each packages so it is not so easy to change. Custom component should do the trick but as I want to use only build-in features I will experiment with subpackages or SQL Task and see if I can reduce dependancies.
- You have to refresh two script components each time you add, rename or remove a variable. Very easy to miss but also very easy to do.
- Variables that relate to connections are filled in and at the end they are emptied at design time for unit testing purposes but you have to set certain tasks to DelayValidation = True
- To overcome this issue I will write some scripts that will validate .dtsx (package) file by finding XML part that holds all variables in packages and checks if both scripts are in sync. This script will be part of testing / deployment validation code to ensure everything is fine and will be used to embed additional validation to avoid common human errors that are natural.
Let me think what you think about this approach and if you find any extract advantages, disadvantages and limitations of this approach.
Hope that helps