locked
Config File RRS feed

  • Question

  • I have created a SSSIS Pkg with a SQL Server connection. I created an xml config file in and enabeld it.
    The config file has the 'ConnectionString' property which looks like the statement below.
    I wanted to confirm if the SSIS Pkg is reading the config file . SO I Intentionally changed the config filename and
    put an incorrect filename on the config file. So SSIS pkg has one filename as config file and the actual name is diff.  However the pkg still works just fine which makes me believe that the pkg is NOT reading the Config value.
    Pl advise  how can I ensure that pkg reads the config file or make it read the config file?
    Thursday, August 20, 2009 8:05 PM

Answers

  • If you have changed the filename of the configuration file, and run the package, you should see a warning message that says "Failed to load at least one of the configuration entries for the package ..."

    In that case, it will just run the package without using any configuration (i.e. the ConnectionString that you have specified in design time will be used).

    Thanks.

    • Proposed as answer by weehyongEditor Friday, August 21, 2009 9:04 AM
    • Marked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    • Unmarked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    • Marked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    Friday, August 21, 2009 9:04 AM
    Answerer
  • what is the error message ??? please share that would help debuging ???
    can u tell how are u using the xml configuration ???
    share ur config xml file ....
    are the variable name EXACTLY same  in both package and the config file ?
    after adding the package configuration is the connection string variable populated with correct value ???
    share the connection string for your OLD DB connection.. is the format correct ???
    if u can share the error message that would be of great help to identify ..
    i doubt u r missing some silly point ...
    are u able to create correct connection manager successfully at first place ?? without configurations ???

    • Marked as answer by Desigal59 Thursday, August 27, 2009 5:56 PM
    Thursday, August 27, 2009 6:28 AM

All replies

  • Package will work fine with or without Config file in the local enviournment,
    If you have enabled the configuration propery it should read it , If you want you can use the  comamnd line to test it.
    dsu
    Thursday, August 20, 2009 11:20 PM
  • I developed the pkg on my pc, but the sql server connection is pointing to dev server. So it is NOT runnin in local enironment.
    Friday, August 21, 2009 3:48 AM
  • If you have enables package config it should read from the config table.

    Even though u have configured the SQL server conn to dev server but executing the package in BIDS in ur PC it will run in ur PC and connect the
    SQL Server to fetch values when required.

    Hope this helps !! Please mark the post(s) as “Answered / Helpful” that helped you - Sudeep
    Friday, August 21, 2009 4:23 AM
  • If you have changed the filename of the configuration file, and run the package, you should see a warning message that says "Failed to load at least one of the configuration entries for the package ..."

    In that case, it will just run the package without using any configuration (i.e. the ConnectionString that you have specified in design time will be used).

    Thanks.

    • Proposed as answer by weehyongEditor Friday, August 21, 2009 9:04 AM
    • Marked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    • Unmarked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    • Marked as answer by Desigal59 Friday, August 21, 2009 6:06 PM
    Friday, August 21, 2009 9:04 AM
    Answerer
  • hi desigal
    see if u give package config correctly it will pick the configurations correctly if ur variables name and all correct and same ...
    what might have happened that u changed the file name but the initail value that were loaded from previously correct config file will be retained .....
    instead to test u should have changed the configuration in the config file and reloaded the packaged the changes in config file would have reflected in the package ... U try out that first ....
    or do one thing just delete the connectionstring value from the package and reload it ur package will not run fine now ... since u have changed the config file name [ie the config file defined in package configuration is literally not existing .... hence it would fail ... ]
    check out
    http://msdn.microsoft.com/en-us/library/cc895212.aspx from the moderators ... for pacakge configurations ..
    Friday, August 21, 2009 9:45 AM
  • Ok, I have a correct config file with the correct Connection string properties, pointing to the rite server and db name. So to test, I opend up the ssis pkg, and in the connection manager, renamed the servername to an incorrect servername. So I was hoping that though the ssis pkg has incorrect servername, the servername would be pickedup from the connectionstring prop in the config file and shud work. However the sssi pkg errs.
    Wednesday, August 26, 2009 3:50 AM
  • mam
    Could u tell in details how exactly u  are using the config file ??? and the configurations defined in it .....
    is ur connection manager really picking up the connection string from the package variable ??? .....
    If correctly done then for testing stuff do reload ur package after manipulating the connection strings .... then run the package
    what do u mean by " I opend up the ssis pkg, and in the connection manager, renamed the servername to an incorrect servername." did u change the connection strings of the connection manager or  in the manager it self .....
    Wednesday, August 26, 2009 5:24 AM
  • Hi Desigal,

    Just one basic question (may sound silly). Is the enable package configuration check box is checked?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 5:45 AM
  • I renamed the servername to an incorrect servername, in the connection manager itself.. When you say is ur connection manager really picking up the connection string from the package variable ??? ..... Besides, enabling the configuration ie do I need to do anything else to the connection strings within the connection manager in BIDS?
    Wednesday, August 26, 2009 1:57 PM
  • I renamed the servername to an incorrect servername, in the connection manager itself.. When you say is ur connection manager really picking up the connection string from the package variable ??? ..... Besides, enabling the configuration ie do I need to do anything else to the connection strings within the connection manager in BIDS?

    If you rename the connection manager (if it was eariler DBCONN and you rename it to DB_CONN).
    The entry is for DBCONN in your config file and not for DB_CONN.
    When you eill execute the package it will try to locate DB_CONN in the config file (which is not there).
    SO package will fail

    If you rename the DBCONN in the config file to DB_CONN the package will not fail
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 2:03 PM
  • PL NOTE I Did NOT rename the NAME Of Connection Manager. I renamed the SERVERNAME That is within the Connection Manager. So I was hoping that since the servername in the COnnection manager is incorrect, it will pickup the servername from the Config file since the servername in config file is correct. But looks like it is Not readng config file, since the pkg is erroring.
    Wednesday, August 26, 2009 3:24 PM
  • Ok, I have a correct config file with the correct Connection string properties, pointing to the rite server and db name. So to test, I opend up the ssis pkg, and in the connection manager, renamed the servername to an incorrect servername. So I was hoping that though the ssis pkg has incorrect servername, the servername would be pickedup from the connectionstring prop in the config file and shud work. However the sssi pkg errs.

    Could you please tell us how you are renaming the server name to an incorrect server name through connection manager?.
    Or, you are selecting a different server from the drop down box.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 5:15 PM
  • Ok i guess i got it ...
    see it doesnt matter what name u give to ur connection manager ..... to pick right value from config at run time or any thing u need to set the connection string property in the expression ... here is what u need to do or i believe u r doing ...
    1. Create config  xml file  which i guess u already know refer (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7c2b83b5-0568-4414-8804-ec0af0c153dc links given in it also from rafel ** ) in the config file there should be a variable configuration as sat "conMgrString " set value to whatever say if flat file connection manager it is then ... "C:\source.csv" ....
    2. In the package add a variable as "conMgrString ".
    3. Package --> SSIS --> package configuration add xml configuration how : check out the link .
    4. now create connection manager (whatever type ) as i have given example of flat file ... so right click in connmanager area create new connection manager give whatever name u follow .
    5. Now right click the connection manager created click properties .. go to expressions --> add select connection String property and pick value from variables as User :: "conMgrString" which  we have recently created .... to confirm click evaluate expression U should be able to see the value that u have given in config file as well as the value should be reflected in the package variable also when u reload the package after adding the package configurations ...
    thats it job done ..... what ever u do with the value of the connection string variable in the package ... open the conn manager ... do whatever u want ... if its picking up value properly [the way i have suggested ....] the package should be fine .....
    can u share  how are u doing ur package configurations and making the connection manager configurable .. that way we would be able to debug more efficiently ...
    --------------------------------------------------------------------------------------------
    hope that helps your query . please mark post/s as answered if that answers ur querry - kunal
    Wednesday, August 26, 2009 5:35 PM
  • Hi Kunal,
    I Followed your steps. However, it still errors" Err message is Error at Data flow task: There were errs duing task validation.
    Wednesday, August 26, 2009 8:36 PM
  • what is the error message ??? please share that would help debuging ???
    can u tell how are u using the xml configuration ???
    share ur config xml file ....
    are the variable name EXACTLY same  in both package and the config file ?
    after adding the package configuration is the connection string variable populated with correct value ???
    share the connection string for your OLD DB connection.. is the format correct ???
    if u can share the error message that would be of great help to identify ..
    i doubt u r missing some silly point ...
    are u able to create correct connection manager successfully at first place ?? without configurations ???

    • Marked as answer by Desigal59 Thursday, August 27, 2009 5:56 PM
    Thursday, August 27, 2009 6:28 AM
  • There is now an utility available in CodePlex which can be used to batch update SSIS Package Configuration File paths without using BIDS:
    http://ssisconfigeditor.codeplex.com/
    Saturday, May 19, 2012 6:08 AM