Hello, I have following problem: I have many databases (around 20) on 5 different servers. I have get some data from this databases. My idea is: I have created one table with data for creating connection string for each database. I create them in script task and store in variable. It works fine. Main problem is, how i can create connection, where i can change my connection strings and which I can use with Execute Sql Task. Could you advise me? Thank you for your reply. Zdenek

Have a table with server name and database name and use an execute sql task to fetch these values in an object variable. After that use a for each loop to iterate through these values (by setting the enumerator as foreachado and saving the server and database name in string variables). Inside for each loop use the data flow task and configure it by creating a connection manager. Once data flow task is configured go to the connection string property of connection manager (select connection manager and go to its properties. Click on ellipsis against Expression to open the expression builder and select connection string ) and set an expression for it using the string variables (used to save the server name and database name in the foreach loop). Ex: "Data Source="+ @[User::ServerName]+";InitialCatalog="+ @[User::DBName] +";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package1;" Do not forget to give proper default values to the variable ServerName and DBName. Also, note that this will work as long as the table name and the structure is same. Nitesh Rai- Please mark the post as answered if it answers your question

Thank you for you reply. So my progress was right. I have it as you describe, but i have some things different...I don't use data flow task. I use Execute SQL Task. Could you tell me, how i have to configure data flow excatly? How i have to create new connection and e.t.c. I think, that i have to do something wrong there... Thank you.

You can do exactly as Nitesh described, substituting your Execute SQL Task for the Data Flow Task in the For Each container. The key is that the connection manager connection string expression is based on the variable assigned in the For Each loop. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

I am sorry, but I have problem with data flow. I have added data flow in for each container. There I create OLE DB connection. As connection I set up myConnection manager and as source of sql command i set my variable with sql command. when i tried click to preview, i have received message "The AcquireConnection method call to the connection manager "(Connection name)" failed with error code 0xC0202009". Where can be problem? myConnectionString is: Data Source=ad368109pc;Initial Catalog=ITPortal;Integrated Security=True;Provider=SQLNCLI10.1;Auto Translate=False; I am using windows authentification. Thanks.

What have you decided to use inside the foreach loop container...execute sql task or data flow task. As Dan Guzman said you can use execute sql task also with the same approach. Coming to your error message: Were you able to define the connection manager successfully? Does the variable used for sql command has some proper default value?Nitesh Rai- Please mark the post as answered if it answers your question