Hi guys, I'm unable to find my problem here so I apologize if I'm reposting it. My situation is: I have 3 DBs that have same tables but different data. The thing is, that I've implemented a ETL process that enters a foreach loop w/ the result of a table that contains: a load flag, server and initial catalog (and other stuff that doesn't apply here). I'm loading the result of the query in variables, and then concatenate them with the connection string. Variables work fine as I can see them loaded in the debugger, but when it comes to execute the Data Flow that has the query for the source, it fails. Any ideas? I've been searching and read something about "expressions are loaded before execution" so it might be that variables after loaded are not concatenated to de ConnectionString. Another thing is that DelayValidation of the package is set to True in order to run. Thanks in advance, Alan

Is this what you are doing? 1. Execute SQL Task: Select servername,initialcatalog from the table and save the full result set in an object variable. 2. Iterate through the object variable using For each loop and save the server name and the initial catalog value in string variables. 3. Have data flow task within the for each loop. Set an expression for the connection string property of the connection manager using the variables (used in Step2) as shown: "Data Source="+@[User::ServerName]+ ";Initial Catalog="+@[User::InitialCatalog]+";Provider=SQLNCLI.1;Integrated Security=SSPI;Application Name=SSIS-Package1-{A6D16A21-39C3-436C-B12A-31C21D8F5CEE};Auto Translate=False;" 4. Give some valid default values for the variables to configure the data flow task.Nitesh Rai- Please mark the post as answered if it answers your question

I will restate your scenario to be sure that I understand correctly. You have an SSIS package that has a Connection Manager using an Expression-based ConnectionString. That Expression is set to a variable, such as @[User::NewConnectionString] You read from a table containing the names of the three databases. You are using a ForEach Loop to process each of those databases. In the ForEach Loop configuration, by setting a variable to the name of one of the databases, your @[User::NewConnectionString] value is set to a valid pointer to one of those databases. Please confirm that this is the scenario, and please post the error message that you are getting. If you want to do some digging yourself, try using a static ConnectionString instead of the Expression. If the code works from there, you can be sure it's something in the Expression usage. Also, be sure that any package variables that you have defined as Expressions (their Expression properties are not empty) have their EvaluateAsExpression property set to True.

What errors do you get? Make sure you set the DelayValidation property of the dataflow to true.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Hi Nitesh, That's the scenario. Variables doesn't have valid values. I mean, I'm testing it putting "ServName" instead of "ServerName", just to make sure that the variable that has the result of the SQL task is working properly. The other 3 steps are as I have them. Thanks for making it clear for everyone to understand the situation.

Thanks for the confirmation. Does the package work with the Connection Manager set to a static Connection String?

DelayValidation is set to True, because if it wasn't, Package won't start.

Hi Randy, The Scenario is like Nitesh described it. The error message is that the ole db source component failed. Nothing else.

Yes, it works fine. Now the Expression isn't working, and when i set variables with the name that's gonna be given as result from the earlier query, it works. So it doesn't seem to be a problem of concatenating strings.

Were you able to configure the data flow task without supplying default values to the variables?Nitesh Rai- Please mark the post as answered if it answers your question

Yes because it was static (connection string) now that it's variable, i set delay validation to true in order to make it work. But anyways, it doesn't seem to work.

alan, two things from you please: The value of the conn string variable just before it needs to be consumed, and The exact (omit any security items) error message AND warnings (if any) that you get. Also, it would not harm to post a picture of your designArthur My Blog

Solved. Insted of using concatenate to make the connection string like Nitesh explained, I just put User::Server and User::Catalog in the texboxes of Properties (Connection Manager). IDK why it was solved but anyways, thanks for the help. WHen I have more time (things are burning here) I'll post a screenshot.

Solved. Insted of using concatenate to make the connection string like Nitesh explained, I just put User::Server and User::Catalog in the texboxes of Properties (Connection Manager). IDK why it was solved but anyways, thanks for the help. WHen I have more time (things are burning here) I'll post a screenshot.