Package Configurations - DTSConfig File
SSIS supports 5 different types of sources to use the setting the properties of objects and they are :
- XML Configuration File
- Environment Variable
- Registry Entry
- Parent Package Variable
- SQL Server
I have used XML configuration file to do all the SQL Server connection settings. In the future if you wish to include more connections / settings you can add to this configuration file. If you continue to use this method, you can execute SSIS Packages in different environments without making any changes to the packages. Environments.dtsconfig is the only file you would have to change between environments.
Configuration file is located at C:\Config\Enironments.dtsconfig
This configuration file should exist at the same place in all environments. During runtime SSIS package will pickup the setting from that location and update the connection settings according to the XML configuration file.
XML configuration file will look like the one in the link below:
When you migrate the SSIS packages from one environment (Dev) to the other (UAT), you would need to do the following to the configuration file :
- Copy “Enironments.dtsconfig” from your dev environment to UAT environment. Please make sure that you place this configuration file in the same location as development.
- Edit “Enironments.dtsconfig” using WordPad or something similar.
- Rename Data Source values. For example if your development environment is called “Beast\SQL2005” and UAT environment is called “Sql2005\Uat”. You need to search and replace the value for data source.
- Save the file.