4

I have to create a Windows Form application which would modify the connection sring values present in dts config file and then execute the package with this updated dts config file.

Even if I am adding following line of code,it is not taking the updated config file.

string packagePath = ConfigurationSettings.AppSettings["packagepath"].ToString();
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
 Package package = app.LoadPackage(packagePath, null);
 package.ImportConfigurationFile(configPath);
 DTSExecResult dtsResult = package.Execute();

Please help how to execute the package with modified dts config.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Shipu
  • 543
  • 14
  • 27

2 Answers2

2

You need to read the dtsConfig file, which is an XML file, using the .NET XMLDocument object and then modify the appropriate node containing the connection string with the new value.

Example in the following MSDN forum shows how this can be done:

Setting SSIS package properties programmatically

Personally, I use database table to store package configuration values. I feel that is much easier to maintain than the xml configuration files (dtsconfig).

  • +1 on the database table to store configuration values. In combination with an xml file with only the connection details needed to connect to the database where the configuration values are stored to ensure your packages are not environment-bound (DEV/TST/PRD) – stombeur Sep 07 '11 at 12:41
1

Check this thread out:

SSIS how to set connection string dynamically from a config file

Community
  • 1
  • 1
bbqchickenrobot
  • 3,592
  • 3
  • 45
  • 67
  • In the mentioned thread the config file is added at the time of creation of package.But my need is as below. – Shipu Sep 05 '11 at 09:49
  • 1. I have a dtsconfig file at shared location which the package points to. 2.I take user input from user using a windows form and update the connection string of this config file and save it. 3.Now i have to execute the package with this modified dts config file. Regards, Shipra – Shipu Sep 05 '11 at 09:51