How to make my SSIS connections dynamic so I can easily switch to different environment (e.g. Dev, QA, Prod …)?
In DTS if you want to make your connection attributes dynamic (i.e. Server, UserName, Password etc.) then you can use UDL files or Dynamic Properties Task but none of them were effective and they had their own drawbacks. In SSIS Microsoft came up with a concept called “SSIS Configurations”. You can read configuration settings from any of the following source. Read from XML File Read from SQL Server Table Read from Registry Key Read from Environment Variable Read from Parent The most common way of reading configurations is “XML Config File” (see below screenshot). If you use DTS xChange then configuration files for selected Connection Types will be automatically created during migration. DTS xChange is smart enough to detect all your common connections based on connection attributes (i.e. server, username, password etc.) and generate one config file which can be referenced in many packages. You can easily modify Config file and all your packages using that Config file will start pointin
