I need to consolidate data from 1000+ databases having the same structure / tables in one unique database. Databases can be added and deleted potentially on a daily basis, so I need to dynamically retrieve a list of databases and run a dynamically created SQL query to retrieve data for each of them.
I developed a data stream with a request from a variable that works fine if executed with a static value:
With the SQL task, I get a list of instances, I iterate over them, and using the Loop / SQL Foreach nested task, I get the database names and create dynamic SQL with the following statement (the database name is anonymous):
SELECT 'select' '' + name + '' 'as DatabaseName, ID from' + name + '. [dbo]. [Orders] as querytext FROM sys.databases WHERE name LIKE ('XXX% _%');
This part also works fine:
How can I use the result of the SQL task “Executing an SQL task - Get query text” as a query that must be executed in the “OLE DB Source 1” source (part “Task 3 of the data stream”)?
I tried to map the Object variable "SyslCommandFromSQLTask" in the result set of the SQL task, and then set it as the ADO object source variable and use the Script task to convert it to a string and pass the value to the SqlStringFromSQLTask3 variable (used as the source in "OLE DB Source 1") , but I get the error "PRIMARY KEY constraint violation", for example, if the data stream always works with the static value set by default:
While I delete the value from the variable panel, I get the error message "Command text has not been set for the command object." Even changing the DelayValidation property of the data stream to false.
Any help is greatly appreciated.
source share