you have to run the same SQL statement(s) in multiple database server/instances and you want to do it via SSIS.
Here is an approach
You can solve this issue in 3 steps:
1. Within a package, you can load all the connection strings of the target DBs in a SSIS variable of object type
2. Then you can use a ForEachLoop container to shred the content of the object variable and iterate through each of the connection strings.
3. The rest is just plain easy, place the Execute SQL task(s) with the SQL statements you have to run in all the target instances inside of the ForEachLoop container.
Where is the trick?
Use the power of SSIS expressions! Add an expression to the Connection manager used by the inner Execute SQL Task(s) to alter the connection string on each iteration with the content of a variable populated by the ForEachLoop container. For this example I use a SQL table to store the connection strings, but you could stored them in a flat file or any other place where SSIS can get a connection to. In other instances you may find more practical to write a query that list all the instances, but my preference is to store the values so I have more control over the process.
Here is the structure of the table I used:
.png)
This is how I configure the ForEachLoop container:
.png)
.png)
This is the configuration of the outer Execute SQL task
.png)
.png)
This is the configuration of the inner Execute SQL task
.png)
you can download the sample package from my Skydrive (you will need SSIS 2008):
I hope you find this helpful. Ah! and if you have questions about this post or any other SSIS issue I recomend you to post your questions in the SSIS forum: Here
Raf.
When I execute simple SELECT statement in the inner execute sql task, it works fine. However when I use MERGE statement, it is failing. I tested the same MERGE statement with Execute sql task with a known connection manager (not dynamic), it works just fine.
ReplyDeleteAny ideas?
I don't think the Merge would represent an issue by itself. I would recommend to post the question in the MSDN SSIS forum with the detailed error message and the Merge statement you are using.
ReplyDeleteHi Rafal,
ReplyDeleteI am using the same senario like your example above.
I have list of Instances names in a table, and i have a test table that has to go into all the instances and database names are same for all the Instances (using dynamic connections).
I tried your example but I see only the table going into one Innstance not copying the table to other Instances.
In your example I see you used only one one Instance, SO i couldnt figure it out.
can you please help me out? Its very Urgent.
Hi Rafael,
ReplyDeleteCan this apply on the sub package in SSIS. If let said I have 2 sub package that I wish to called in the Main Package. and the 2 sub package is using different connection.
Whats the link to download the sample package code?
ReplyDeleteHi Rafael,
ReplyDeleteI have to execute a query that fetches data from multiple databases. I am putting this query under Execute SQL Task. It runs fine when I run it on the local visual studio, but throws an error during the fetch operation when I run it via a job on a development server. Can you please advice.
Kabir,
ReplyDeleteMore often than not. This issue is due to security and permissions in the machine you are running the package as JOB. Make sure the account running the job has the required permissions.
The problem was with the access permissions on the database server. The queries and the package were all good. Thanks for the reply Rafael.
ReplyDeleteI have tried this concept but my inner loop is throwing the following error:
ReplyDelete[Connection manager "DynamicConnection"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
I have set the ConnectionString property as an expression filled by the result set variable. In the debugger, i set a break point and see the correct connection string. how come the DynamicConnection object is not getting its connection string set?
How about some info about the connection configuration for Dynamic Connection and the expression? I'm having trouble getting the object back to a string...
ReplyDeleteAnswer to venkata question:
ReplyDelete1) Before "Execute SQL St-t" place "Script Task"
2) Add following code to Main:
Dts.Connections.Item("DynamicConnection").ConnectionString = Dts.Variables("User::TargetInstanceConnectionString").Value
3) Connect "Script Task" with "Execute SQL St-t"
Hi Rafael,
ReplyDeleteExcellent Article!!!
In fact I was looking for something like this.
I want to know a few things:
1) How the Dynamic Connection is getting the actual connection string since Initial Catalog is shown as XXXX?
It seems the For EACH Loop is getting it from the variable ALL_TargetInstanceConnectionString and storing it in the other variable. But no link b/w the other variable and Dynamic Connection.
2) I want to get output of the different databases(same query) in different csv files.What shud I do?
Pls help.
S B Ray
Hi,
ReplyDeleteI want to transfer the data obtained from the multiple DB's to flat file each having the DB name.
Pls guide me as to how I may do it.
S B Ray
Hi Raphael,
ReplyDeleteWhere is the link to download the sample packages?
Perhaps I missed something
what if we have db2 and sql sever instances??? how to change connections dynamically?
ReplyDeleteHi rafael:
ReplyDeleteI did exactly what you said except this part Integrated Security=SSPI as I will be supplying sql server credentials.
I did a test and seems the dynamic connection is using the connection string via
the variable except the password.
How to pass password to connection string using your approach? Do I need to pay attention to the package ProtectionLevel?
Thanks
Hui
Hi Rafael,
ReplyDeleteExcellent article, thank you :)