Koen Verbeeck (3/11/2011)
Why don't you store the connectionstring to the configuration table directly in the environment variable?
It was desired to have the ability to update the connection string to the configuration database without having to update the environment variable.
Koen Verbeeck (3/11/2011)
Regarding your last question: if you open the package it makes a connection to the configuration database under your credentials. (unless you specified SQL Server authentication). So maybe the SQL Server Agent account doesn't have read access to the configuration database?
I think this is going to be my focus. We are executing the package via dtexec. I think in this case, the package will be executed by the user context that is set up to run the executable. This user is set up as an admin on the box, so I am curious if there is something that we are missing in the command line. It should be able to read the environment variable.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development