HI I have a requirement that i have to load data from multiple access tables in a database with same structure into sql server table having same structure of source. Please some one help. Regards, Sam

Please check this blog for reference.. let us know if you have any doubt..Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

ETL vs ELTL, Thanks for your reply. I just checked above mentioned blog. It is with one source table to one destination. What i need is i have access database it that i have few tables with same structure. I have a sql server destination table with similar structure. I want to load all access tables data into sql server table. Please advise Regards, Sam

As SSIS don't support the dynamic metadata inside the DFT, you can use single flow for all access tables. Better use script task. OR create separate flow for each and every access tables. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Hi Sam, As I understand, you have multiple tables in a Access database, there tables have same structure. You know how to import Access table to the SQL Server one by one. However, now, you want to know how to get the tables dynamically, and then import data from all tables to the SQL Server table. If I have misunderstand, please don't hesitate to let me know. First, we can use the Foreach Loop Container to loop the Access database to get all tables' name. Inside a Data Flow Task(DFT) to the Foreach Loop Container, and in the DFT, import each data from each Access table to the SQL Server table. Create a variable with a initial Access table name. Add a Foreach Loop Container to the control flow. In the insided DFT, set the 'Data access mode' to be 'table name or view name variable', and set the value to be the variable we defined in step 1. Finish other settings(e.g destination, column transfer). Now, we shoule be able to import data from each Access tables to the SQL Server table. Please note, when use Foreach Loop Container to get the tables' name from an Access database, all system tables will be returned too. We should use Script task and Access API to check if the returned table is a user table. Another easy way to do that is named the user table with prefix, and then use Precedence Constraint to load data from user tables only. For more information, please see: How to: Loop through Excel Files and Tables by Using a Foreach Loop Container: http://msdn.microsoft.com/en-us/library/ms345182.aspx(Get Access table name dynamically is same to "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator") If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT