I'm currently working on a project of building a data mart using SSIS 2008 r2 from ERP database. These datamarts will have to be used in every database that has the same structure (same tables/fields). The problem is that the source table name changes based on the database connected to. But the table structure (metadata) does not change between those environments. For example, Database 1 item table name is company_name1$item Database 2 item table name is company_name2$item I'm looking for a solution that makes the datamart can be used for both databases. I did some research; I found that my solution can be like explained in the picture ![enter image description here][1] As you in the diagram picture, there are steps to follow to build the datamart: - To build a datamart, you will to extract the informations and process them through ETL to have the desired datamart. This procedure needs to be applied to all databases - This procedure can be done by a table in the erp database called object This table has all kind of informations about the database tables but the most useful fields are table id,table name and company name Now, I want to use this table id, Name Company to select the proper table without having to selected data based on table name but there no linking between object table and other tables, so you kind of need to create a some kind of code or something to be able to create the link. *In a nut shell, when I want to use package for a databse , I have insert is the company name and the package will be run to fill my datamart.* As some suggested in other forums, I should use a sql procedure of this type USE YourDataBaseName; CREATE PROCEDURE GetMyData @aCompany NVARCHAR(50) = NULL, @tbl_ID INT = 0 AS SET NOCOUNT ON; DECLARE @tableName NVARCHAR(50) DECLARE @aQuery NVARCHAR(4000) SELECT @tableName = [Name] FROM [object] WHERE [Company Name] = @aCompany AND [ID] = @tbl_ID SET @aQuery = 'SELECT * FROM ' + @tableName EXEC(@aQuery) GO Other one which was also interesting, <b>said that SSIS allows for the use of a variable name in your source and destinations. I just simply need to provide the mechanism for making that value change. Perhaps that's a foreach enumerator containing the data flow or a parameter set at run-time.</b> Both suggested solution are great but still not clear on how to do that , I hope I would get a more specific answer

These posts should guide you how to solve your problem using the second method, with For Each Enumerator: http://www.codeproject.com/Articles/14341/Using-the-Foreach-ADO-Enumerator-in-SSIShttp://www.bidn.com/blogs/MikeDavis/ssis/156/using-a-for-each-loop-on-tables-ssis