Running a package with a configuration file


  • Hi folks:

    I built an SSIS package that adds and updates a database. In BIDS, I used the connection manager to point lookups and database access to the test database. All working fine. Now I am trying to redirect the SSIS package to run against the production database without having to recode all the lookups and database accesses.  I set up a config file in BIDS after reading posts which BIDS is reading as I execute in debug mode but can not get it to run against the production database no matter what permutation or combination I use. Thoughts?

    Tuesday, December 17, 2013 6:32 PM

All replies

  • When you say does not run against production, do you mean you get an error? How do you "redirect"?

    As an aside, ideally, a forum post must include information permitting a responder reproduce an issue.

    Arthur My Blog

    Tuesday, December 17, 2013 6:38 PM
  • No. It runs against the test database even though the config file only has the production database values. Both databases share the same table structures so the only things that differ are the database name (TCS for production versus TCS_TEST for test) and the server names those databases reside on. The config file have the whole connection string in it.

    Sorry about the reproduction issue. I work in a HIPAA restricted environment.

    Tuesday, December 17, 2013 6:55 PM
  • Then the config fails to apply, and you overlooked a warning about that either in the log or console window (if you decided to not to have the log , which in turn is a bad decision).

    Arthur My Blog

    Tuesday, December 17, 2013 7:02 PM
  • Console window message looks normal.

    Information: The package is attempting to configure from the XML file "C:\Users\xxxxxxxx\Documents\Visual Studio 2008\Projects\SSIS\Project SSIS\CloseVendorsConfig.dtsConfig".

    The package runs to completion successfully but does its updates against the test database and not the production database.

    Is there another log file that gets generated separate from what you see in the console window?

    Tuesday, December 17, 2013 7:48 PM
  • No other logs unless you create yourself (from the SSIS menu)

    Is your config file set to consume the prod settings?

    It is also possible that an expression takes over the config value at run time, so the log would help you pinpoint which.

    Arthur My Blog

    Tuesday, December 17, 2013 7:54 PM
  • No expressions taking over the config file. How does one set the config file to consume the prod settings?
    Wednesday, December 18, 2013 1:44 PM
  • Then I suggest dropping the configuration and re-adding it back under a different name.

    Arthur My Blog

    Wednesday, December 18, 2013 4:21 PM
  • Tried that as well. No luck. Also tried editing the config file directly with the production server and database names. Also no luck.
    Wednesday, December 18, 2013 4:42 PM
  • Then recreate this package from scratch, if this is not possible then remove the configuration and key in the prod values.

    I am sure though you are overlooking something, not sure what because you haven't shared steps to reproduce.

    Arthur My Blog

    Wednesday, December 18, 2013 6:50 PM
  • Can do. I will create a test package to figure this out. Should I start with a new project file as well? I will document the steps as I move through the process. I tried to include an image of the test data flow in my last post but got an error message from the forum that my account had not been confirmed. Since I receive emails when you post, I think my account has been confirmed. Thoughts? 
    Wednesday, December 18, 2013 7:08 PM
  • I set up a config file in BIDS after reading posts which BIDS is reading as I execute in debug mode but can not get it to run against the production database

    I believe you have set the XML config file for the package and has included all the connection strings. When you have delpoyed the package the and editited the XML config file with prod conn strings, it should read/connect to prod dbs as per XML file. Did you changed the conn strings in XML file when it was deployed to prod?

    Thanks, hsbal

    Wednesday, December 18, 2013 7:15 PM
  • I built the XML configuration files in BIDS using the test conn strings. I changed them in the XML file to production. The package only runs in BIDS and is never deployed. With Enable Package Configurations checked, the progress log in BIDS states it is reading and applying the config file with no errors but when it runs, it is still running against the test database. There is only one connection string in the configuration file. The package has both test and production database connection managers in it.
    Wednesday, December 18, 2013 7:28 PM
  • Hi Robert,

    If I understand correctly, you created a XML Configurations file for the package in BIDS 2008 and only exported the ConnectionString property for a single Connection Manager.  Then, you changed the ConfigureValue for the ConnectionString in the.dtsConfig file so that it updated the production database. When running the package in BIDS again, it prompted that it was attempting to read configuration from the correct XML file, however, the package still updates the test database.

    Based on my test, the issue may occur if you have specified the target database in the UPDATE statements used in the Excel SQL Task. For example, if you use a SQLStatement like below for the Execute SQL Task, it will update the database specified in the SQLStatement rather than the database specified in the XML Configurations file:

      UPDATE [TEST_DB].[dbo].[TableName]
      SET Val ='test' where ID = '1'

    In this case, we can use the following SQLStatement instead:

      UPDATE [dbo].[TableName]
      SET Val ='test' where ID = '1'

    After that, when we run the package, it will read the XML Configurations file and update the production database that you specified in the .dtsConfig file.

    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support

    Monday, December 23, 2013 6:38 AM
  • I am only doing a lookup. No updates in my package. Can you confirm my account so I can post an image of the data flow? Thanks.

    Bob Senk

    Thursday, December 26, 2013 2:38 PM
  • The package has both test and production database connection managers in it.

    why are you keeping prod conn string in the bids/package? Lets say while package desiging you have conn manager that pointed to test server/db and you tested it in BIDS itself, without any config file. For sure, it will make connection to test db only. Now if you set config file and restest it in BIDS, it will still load/connect to test db because XML config file is not updated/changes. Finally if you run package again in BIDS but edit the file (as per prod server\db) to connect to prod, it should connect to prod only !!!

    Thanks, hsbal

    Thursday, December 26, 2013 8:14 PM
  • I removed the prod connection manager from the package. When you edit the config file that was pointed to the test database with the production values and try running it, the package fails on loading the config file with errors saying it can not connect to the prod database. So the prod connection manager has to be in the package.  

    Bob Senk

    Friday, December 27, 2013 1:19 PM