I tried the XML configuration type and exposed and changed the sql account and password whenever I need to. No problem. However, we need to use SQL Server to store these properties. But it gave me "Login failure for 'sa' ". The 'sa' is the testing sql account that I used and exposed into the [ssis configuration] table. I think it does not see the table because the conn mgr I specified for the "Connection" field during the Package Configuration Wizard is the same one that used for query and write data to the sql table. And that I used "DontSaveSensitive", the password is not available for the configuration to see the [SSIS Configuration] table. So... do I need an XML file to tell where the table is? If so, exactly how? My company guideline is not to use environment variables. Please help.

Create an XML config file and make the conection manager as configurable property. So, you would need an XML file and the sql server config table.Nitesh Rai- Please mark the post as answered if it answers your question

Create XML Configuration file here is good sample http://www.mssqltips.com/tip.asp?tip=1434

OK, so, we agree that I need the XML file. So what's in this XML file? Will it have the sql account and password? Seems redundant to me because I need to create the XML with same SQL account/password so it knows where to locate the [SSIS Configurations] table which will have the same SQL account and password (plus other properties that I elected to expose). This is like a catch-22 situation. Please help explain how XML and SQL Server cfg works together. I tried the XML method by itself and no problem. The pkg knows where the XML file is located and needs NO additional credential to get to it. It goes there to look up the credential for the connection mgr(s) used in the package and any other properties I elected to expose. Thanks in advance.

Hi, After you create the sql server configuration type file, add a xml configuration file and select username & password properties of the sql server connection. So, you can change the sql sever account and password against the xml file. thanks, Jerry