SSIS Configurations At Design Time

There’s an interesting “behavior” that occurs with configurations at design time. The value of the configuration will be saved in your package as the new stored value. I’d seen this before, but never really thought about it, until a poster on the forums questioned why it was happening. To understand it, you need to know how the package is processed when it is loaded in the design environment.

If you have configurations enabled in the package, then the configuration is applied to the package when it is initially loaded. When a configuration is applied, it updates the target property of the configuration. For example, you might have a package that has the Description property set to “No Configuration”. If you add a configuration that sets the Description property of your package to “Has Configuration”, when the package is loaded, the value specified in the configuration (“Has Configuration”) will overwrite the value currently stored in the Description property (“No Configuration”).

If you then close the package without saving, the change to the property is not saved, and it will reflect the original value (“No Configuration”). If, however, you save the package (and remember, running it in the IDE saves it automatically), the new value (“Has Configuration”) will be saved as the value in the package file.

Normally, this does not cause any issues. If you execute a package in production that has a configuration, it is not saved after execution, so the values of the configurations is not save in the package file. However, it can impact you in the development environment. This is one of the reasons that I recommend setting up configurations even in your development environment. If you do that, then you know that regardless of the value the package was saved with, it will have the value of your local configuration each time it is loaded in the IDE. This is also a good reason never to update a package directly on your production server. When you open it in the IDE on the production server, it will retrieve the production configurations. If you save or run it, it will be saved with the production configuration values. This can cause a number of headaches in troubleshooting problems down the road, so I recommend that you don’t do it.

Leave a Reply