Microsoft Access Source

MS Access databases can be connected to via SSIS.

For MS Access sources the Jet (.mdb) or ACE (.accdb) OLEDB provider is used. (Excel uses the same provider)

Using PowerShell:

If SSIS is not an option. Using a PowerShell script to load the data is an alternative. Below is an example that can be used to load MS Access data:

$sql = @"
select * from [TableName]
"@
try
{
    $filename = "DatabaseName.mde"
    $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=${filename};";
    $connection = New-Object System.data.OleDb.OleDbConnection $connectionString;
    $connection.Open();
    $command = New-Object System.data.OleDb.OleDbCommand $sql
    $command.Connection = $connection
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo<<SMOVERSION>>" 
    $ns = 'Microsoft.SqlServer.Management.Smo'
    $svr = New-Object("$ns.Server") "<<DWSERVER>>"
    $db = $svr.Databases["<<DWDATABASE>>"]    
    $tbExistCheck = $db.Tables["<<TABLENAME>>_Temp"]
    if ($tbExistCheck)
    {
        $tbExistCheck.Drop()
    }
            
    $tbl = New-Object("$ns.Table")($db, "<<TABLENAME>>_Temp", "<<DWSCHEMA>>")
    $reader = $command.ExecuteReader()
    $schemaTable = $reader.GetSchemaTable();
    foreach ($row in $schemaTable.Rows)
    {
        $sqlDataType =""
        switch ($row["DataType"].ToString())
        {
            "System.AnsiString" {if ($row["ColumnSize"].ToString() -eq "0") {$sqlDataType = "VarCharMax";} else {$sqlDataType = "VarChar";}; break}    
            "System.AnsiStringFixedLength" {$sqlDataType = "Char"; break}
            "System.Binary" {$sqlDataType = "Binary"; break}
            "System.Boolean" {$sqlDataType = "Bit"; break}
            "System.Byte" {$sqlDataType = "TinyInt"; break}
            "System.Byte[]" {if ($row["ColumnSize"].ToString() -eq "8") {$sqlDataType = "Timestamp";} elseif ($row["ColumnSize"].ToString() -eq "2147483647") {$sqlDataType = "Binary";} else {$sqlDataType = "VarBinary";}; break}    
            "System.Currency" {$sqlDataType = "Money"; break}
            "System.Date" {$sqlDataType = "Date"; break}
            "System.DateTime" {$sqlDataType = "DateTime"; break}
            "System.DateTime2" {$sqlDataType = "DateTime2"; break}
            "System.DateTimeOffset" {$sqlDataType = "DateTimeOffset"; break}
            "System.Decimal" {$sqlDataType = "Decimal"; break}
            "System.Double" {$sqlDataType = "Float"; break}
            "System.Guid" {$sqlDataType = "UniqueIdentifier"; break}
            "System.Int16" {$sqlDataType = "SmallInt"; break}
            "System.Int32" {$sqlDataType = "Int"; break}
            "System.Int64" {$sqlDataType = "BigInt"; break}
            "System.Object" {$sqlDataType = "Variant"; break}
            "System.SByte" {$sqlDataType = "SmallInt"; break}
            "System.Single" {$sqlDataType = "Real"; break}
            "System.String" {if ($row["ColumnSize"].ToString() -eq "0") {$sqlDataType = "NVarCharMax";} elseif (-not $row["DataTypeName"]) {$sqlDataType = "NVarChar";} elseif ($row["DataTypeName"].ToString() -eq "varchar") {$sqlDataType = "VarChar";} else {$sqlDataType = "NVarChar";}; break}    
            "System.StringFixedLength" {$sqlDataType = "NChar"; break}
            "System.Time" {$sqlDataType = "DateTime"; break}
            "System.UInt16" {$sqlDataType = "Int"; break}
            "System.UInt32" {$sqlDataType = "BigInt"; break}
            "System.UInt64" {$sqlDataType = "BigInt"; break}
            "System.VarNumeric" {$sqlDataType = "Numeric"; break}
            "System.Xml" {$sqlDataType = "Xml"; break}   
        }
        if ($row["IsLong"].ToString() -eq "True") {$sqlDataType = "NVarCharMax";} 
     
        $colName = $row["ColumnName"].ToString()
        $dtype = [Microsoft.SqlServer.Management.Smo.SqlDataType] $sqlDataType

        if ($sqlDataType -eq "VarChar" -or $sqlDataType -eq "NVarChar" -or $sqlDataType -eq "Char" -or $sqlDataType -eq "NChar")
        {
            $length = $row["ColumnSize"].ToString()
            $dtype = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType, $length.ToInt32($Null))
        }
        elseif ($sqlDataType -eq "Decimal" -or $sqlDataType -eq "Numeric")
        {
            $precision = $row["NumericPrecision"].ToString()
            $scale = $row["NumericScale"].ToString()
            $dtype = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType, $precision.ToInt32($Null), $scale.ToInt32($Null))
        }

        $colconame = New-Object("Microsoft.SqlServer.Management.Smo.Column")($tbl, $colName, $dtype)
        $tbl.Columns.Add($colconame)
    }
    $reader.Close()
    $tbl.Create()
    $connectionstring = "Data Source=<<DWSERVER>>;<<DWAUTH>>;Initial Catalog=<<DWDATABASE>>;"
    $bulkcopy = New-Object("Data.SqlClient.SqlBulkCopy") $connectionstring
    $bulkcopy.DestinationTableName = "<<DWSCHEMA>>.<<TABLENAME>>_Temp"
    $bulkcopy.BulkcopyTimeout = <<TIMEOUT>>
    $bulkcopy.BatchSize = 4000 # Batches of 4k rows is usually fast and keeps memory usage low
    $bulkcopy.EnableStreaming = 1
    $command.CommandTimeout = <<TIMEOUT>>;
    $readrow = $command.ExecuteReader()
    $bulkcopy.WriteToServer($readrow)
}
catch
{
    write-error $_.Exception.Message
}
finally
{
    $connection.Close()
}
[System.GC]::Collect()