Dynamically import Single mdb file with multiple tables into SQL Server using SSIS?

Question

I have a single MS Access .mdb file that contains multiple tables like

Table name:

UserLog_1_2019
UserLog_2_2019
User_Log_3_2019

For every month new table generate automatically.

I want to import above mentioned dynamically tables into SQL Server by using SSIS. How to achieve this scenario?

Thanks, Lawrance A

Any chance of normalizing the data in the source Access DB to use one log table, with a field for User ID? — HardCode, Feb 28 '19 at 20:30

Hadi · Accepted Answer · 2019-03-05T22:04:46.970

In this answer i will provide 2 approaches to achieve this:

using SSIS
using C#

(1) SSIS approach

You must get the Access tables list into a variable of type System.Object, then loop over the tables names using a Foreach Loop container. There are many links that gives detailed informations, as example you can refer to the following SO answer it can gives you some insights:

How do I programmatically get the list of MS Access tables within an SSIS package?

(2) C# approach

Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table having a different schema definition using schema mapping approach. check it out at:

C# Schema mapping class library

You can use a similar code:

string con = @"Data Source=.\SQLInstance;Initial Catalog=tempdb;integrated security=SSPI;";

using (SchemaMapperDLL.Classes.Converters.MsAccessImport smAccess = new SchemaMapperDLL.Classes.Converters.MsAccessImport(@"U:\Passwords.mdb"))
{
    using (SchemaMapperDLL.Classes.SchemaMapping.SchemaMapper SM = new SchemaMapperDLL.Classes.SchemaMapping.SchemaMapper("dbo","passwords"))
    {
        SM.CreateDestinationTable(con);
        //Read Access
        smAccess.BuildConnectionString();
        smAccess.getSchemaTable();

        foreach (DataRow schRow in smAccess.SchemaTable.Rows)
        {
            string strTablename = schRow["TABLE_NAME"].ToString().Trim('\'');

            DataTable dt = smAccess.GetTableByName(strTablename);
            bool result = SM.ChangeTableStructure(ref dt);

            if (result == true)
            {
                SM.InsertToSQLUsingSQLBulk(dt, con);
            }
        }
    }
}

Thanks....but I want to write query like SELECT * FROM UserLog_* or I should pass the table name into variable.. — Lawrance Amburose, Mar 01 '19 at 09:20
You can do both methods. But the easiest is passing the table name into variable — Hadi, Mar 01 '19 at 09:21
Exactly...I followed the Option 1 - SSIS Approach and got the expected solution — Lawrance Amburose, Mar 01 '19 at 09:38
Is there any way to use SQL Query to load specific tables from .mdb files? — Lawrance Amburose, Mar 04 '19 at 09:34
You can write a simple select query using `SQL Command` access mode in OLEDB Source — Hadi, Mar 04 '19 at 16:20

Dynamically import Single mdb file with multiple tables into SQL Server using SSIS?

1 Answers1

(1) SSIS approach

(2) C# approach