Search code examples
sql-server-2012ssis-2012xml-configuration

Change XML configuration path for all packages


I have defined SSIS package configuration to XML and configure it for 50 packages. The xml path for those packages were

H:\SomFolder\Configuration\XMLConfig

but in production server we don't have H: so I have created a folder on D:

D:\Configuration\XMLConfig

How can I change all packages to now refer to new path without opening each and every package and manually configure them?


Solution

  • You have hard-coded the xml config file path in each package and relative folder path of files is same for each package as per my understanding. Simple way is to loop through each ".dtsx" file (SSIS package) and find the string DTS:ConfigurationString="H:\SomFolder\Configuration\XMLConfig and replace it with DTS:ConfigurationString="D:\Configuration\XMLConfig with some simple program. Then, you can open the project\s having these SSIS packages and save it which will be ready to deploy on production.

    This is general problem people face while developing the SSIS packages. Better way to avoid this issue is to store the xml file location in environment variable, so that you can keep the config files on different location on different machines and environment variable with same name will be present on those machines with different file location.