Execute query whenever a combination of databases is found

Question

I have an interesting problem: I need to execute a query over two databases in the same server:

select a.id, b.name
from COMPANY_ORDERS.Table a 
left join COMPANY_USERS.Table B on a.id = b.id

To be extra clear: the tables are always! the same name, it are the databases that have different names.

That is not so hard, they are on the same server. But now! I have several of these pairs e.g.:

MICROSOFT_ORDERS joins on MICROSOFT_USERS
IBM_ORDERS joins on IBM_USERS

How do I get the query to dynamically execute on each pair of COMPANY_ORDERS and COMPANY_USERS?

(Or even better, How would I do this in SSIS 2012?)

Thanx for thinking with me!

Answer 1

As per "Data Masseur", you can use INFORMATION_SCHEMA.TABLES to generate dynamic queries. Hope you looked into this as this will be required for SSIS solution option below.

For SSIS ..

1. use "Execute SQL Task" - Use query to retrieve a list of Company tables from database 1. Set to return full resultset and save into variable (Variable should be Object type)

2. Add "Foreach Loop Container" and connect "Execute SQL Task" to "Foreach Loop Container". In Foreach loop Editor - Collection - set Enumerator to "Foreach ADO Enumerator" and "ADO object source variable" to variable (Object type) that was used in "Execute SQL Task". In Foreach Loop Editor - Variable Mappings to save each value to a variable of string.

3. Add a "Data Flow Task" into "Foreach Loop Container". In the Data Flow Task you should be able to use the variable that is holding the company table name to retrieve the database 2 table name and construct your dynamic query to do whatever you need.

Sorry but do not have enough points to post any pictures.