I recently discovered that SSIS 2005 package variables have and order associated with them, so, if you need to use a value that is been populated after another, the first one will not work.
An example for this is in the use of a environmental variable for passing the connection string for the DB to be used (this is my case scenario).
Trying to build an environmental variable to do this is taking me a while and still with no success.
My steps where to create a Win XP environmental variable and set it's value to the connection string of my OLEDB connection.
This being done, I them created an package variable of string type and, using package configurations, set it's value to be field using the system environmental variable previously created.
So far my logic seems correct but the package keeps falling with user can not log in error.
I'm also using the package security property "do not save sensitive information" because I do not want to associate my packages neither to my account or to a package password.
The idea is for the package to be run at the production machine with the production user and not mine.
Advice is welcome and I will try to post new results.
Found by using the system variable and passing the connection with the correct configuration:
Provider=SQLNCLI.1;Data Source=DATASOURCE;Integrated Security=SSPI;Initial Catalog=DATABASE;
If you require to pass the login and password (in my case)
Provider=SQLNCLI.1;Data Source=DATASOURCE;Persist Security Info=True;Password=PASSWORD;User ID=USER;Initial Catalog=DATABASE;
Doing this and guaranteeing that this variable is the called before all others makes it work.