I am using SQL Server configuration in an SSIS pacakge. Other than when you create/edit the SQL Server configuration entry in the SSIS Package Configurations, are there other times that SSIS writes to the SQL Server table?
Reason I am asking is because what happens if I modify the connection manager the SQL Server Configuration is using by pointing it to a different database (same table name).
Will the SSIS Pacakge Configurations notice this right away or at run time or when the package is re-opened and then correctly create or update the SSIS Configuration table in the new Database that the connection manager is now pointing to? Or do I have to go into SSIS Pacakge Configurations and then back out so that it will refresh?
Best Answer
The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table.
Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would only become effective for the next run. Once a package is actually running, the configuration table could be dropped as it doesn't periodically poll the table to investigate values.
If you modify the value of a Connection Manager in your package, assuming that CM is also under Configuration, the next time the package attempts to validate itself at the package level, it'll reach out to the configuration resource (table, XML, etc) and overlay with the configured values. I think I remember that if you are just right clicking and executing a task within a package, it doesn't make the hop but don't quote me on that.