SQL2005 SSIS package ForEach Loop Container Problem when Deployed

My Package has 3 OleDB Connections
The main oledb connection is for the ForEach LoopContainers query
the others two are set to get their connection strings from a variable that the above query populates

It appears that the forEach Loop is not populating the variables correctly so the connection string is failing. The variable is a string and I have its value set to the dev server. In the foreach loop Editor I set the variable mappings but its not changing the variable

When I use the ForEach Loop with the ADO Enumerator selected, I usually use the Recordset Destination to store data into a variable.  I don't know if maybe the Execute SQL Task's full result set doesn't store the data into the variable the same way.  I know the full result set option does create an ADO recordset so it should work, but I don't remember if I've ever used an Execute SQL Task to fill a recordset.

Try creating a data flow task, add an OLE DB Source component, and a Recordset Destination.  Then try putting the same select statement into the source, and consuming it into the Recordset Destination.  I'm not 100% sure it'll fix the problem, but that's what always works for me.

I tried your suggestion and it still does not work. The variable are not being over written and if I leave the value of the variable empty - I get validation errors on the two oledb connections that use those variables as connection strings. If I copy the connection strings from the table where the variables are to be populated from and put that in their values, everything works great.

I have followed every example on the internet that I could find about Dynamic Connection strings for OLEDB connections and for some reason its not working.
For some reason the Variable Mapping on the ForEach loop is not working to populate variables for OLEDB connection strings

You need to have an existing string set in the variable before running the package.  Here's a sample I wrote that works correctly.  If you removed the value from the variable in the variables pane, and tried to run it, it won't work correctly.

Just remove the .txt at the end of the name.

I really appreciate your input

Our package is almost identical and I am doing the same thing.

The difference is that if I change what you have as ConnList to a different server than what is in the Dynamic Test connection , it still runs the dataflow on the server thats in the Dynamic Test Connection and not on the server in the connlist. The ConnString Variable is not changing its value

Its simply not working - there is something wrong - I think maybe I need to open a case at microsoft because Im getting no where

Do you maybe have RetainSameConnection set to True on the connection manager?  I tried changing the server in my connection and it works fine.  We have a Dev, QA, and Production server at work, and I was able to dynamically change between the Dev and QA servers using this same method.  What I did though which you may not have done is I changed the variable to point to the Production server before changing the data flow task and running the package.  That way, it was able to correctly find a server at first, but then the value was changed as the ForEach Loop ran.

I think the important thing to note is that the variable appears to require a correct connection before running the package.  I don't know if DelayValidation would help on either the Data Flow Task or Connection Manager, but it could be worth a shot.

Are you setting the variable to a valid connection before running the package?  Maybe setting the variable to (local) and master first, and then changing it after.  Also, at that point DelayValidation may need to be set to true on any Data Flow Tasks.

RetainSameConnection is set to false and change the delay validation to true and tried that.

It seems that the connection strings for the variable must be set to the server you are deploying to before you run the package and then it will change the variable . It checks the connection managers first before attempting to run the package.

I was hoping it would just get the connection strings from the table I specified but I guess not. So maybe I need to setup a config file so that I can deploy it to different servers.

There is also a DelayValidation on the connection manager itself which might solve your problem (unless you already tried that one).  I didn't try that but that might work.  My issue was that I changed the select statement in the source component, so I had to set the connection to the table I was referencing.  It basically forced me to set a correct string.  I'm wondering though that if I created the data flow task correctly, and then turned on DelayValidation on the connection manager, if it would have worked then.

If not, it just seems that the connection string has to be set to something that exists, either through a package config file or statically in the properties.  I use this same method for a package I wrote, but it changes the connection to an Access database, so it's different.  I did set that to an existing database before deploying it though.

I set DelayValidation everywhere and it did nothing to help. I also created a xml config file. Set the connection manager connection string property of all 3 connection managers and still did not work. If you do not compile the package with the production server where its going to run , then it doesnt work.
So for my deployment I am going to have to make sure the connection string variables have the correct connections to the server where it will be deployed. Only way I can get it to work.