如何在未知MS Access表结构时用SSIS脚本任务动态导出SQL Server表？

Hey there! Let's figure out how to build that dynamic SSIS script task to load SQL Server data into MS Access when you don't know the Access table structure upfront. I've worked through similar scenarios before, so here's a step-by-step breakdown that should work for you:

First, we need to get the full schema of your SQL Server table—this tells us exactly what columns and data types we're dealing with. In your SSIS Script Task (I'll use C# for examples, but you can adapt to VB if needed), use a SqlConnection to fetch this metadata:

using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

public void Main()
{
    // Use SSIS variables here instead of hardcoding for reusability
    string sqlConnStr = Dts.Variables["User::SQLConnectionString"].Value.ToString();
    string sourceTable = Dts.Variables["User::SourceSQLTable"].Value.ToString();
    
    DataTable schemaTable = null;
    
    using (SqlConnection sqlConn = new SqlConnection(sqlConnStr))
    {
        sqlConn.Open();
        // Retrieve column schema for the target SQL table
        schemaTable = sqlConn.GetSchema("Columns", new[] { null, null, sourceTable });
        sqlConn.Close();
    }
    
    // Pass schema data to next steps
    // ...
}

The schemaTable will include critical details: column names (COLUMN_NAME), SQL Server data types (DATA_TYPE), max length (CHARACTER_MAXIMUM_LENGTH), and nullable status (IS_NULLABLE).

Since the two databases use different data type systems, we need a translation layer. Create a dictionary to handle common mappings—tweak this based on your specific data types:

var typeMap = new Dictionary<SqlDbType, string>
{
    { SqlDbType.Int, "INTEGER" },
    { SqlDbType.VarChar, "TEXT" },
    { SqlDbType.NVarChar, "TEXT" },
    { SqlDbType.DateTime, "DATETIME" },
    { SqlDbType.Decimal, "DECIMAL(18,2)" },
    { SqlDbType.Bit, "YESNO" },
    { SqlDbType.BigInt, "BIGINT" },
    { SqlDbType.Float, "DOUBLE" },
    { SqlDbType.VarBinary, "OLEOBJECT" },
    { SqlDbType.VarChar, "MEMO" } // For VARCHAR(MAX) cases
};

For variable-length types like VarChar, we'll append the max length later (e.g., TEXT(50) instead of just TEXT).

Now we'll build a CREATE TABLE statement using the schema data and our type mapping, then execute it against Access via OleDbConnection:

string accessConnStr = Dts.Variables["User::AccessConnectionString"].Value.ToString();
string targetTable = Dts.Variables["User::TargetAccessTable"].Value.ToString();

StringBuilder createTableSql = new StringBuilder($"CREATE TABLE [{targetTable}] (");
List<string> columnDefinitions = new List<string>();

foreach (DataRow row in schemaTable.Rows)
{
    string colName = row["COLUMN_NAME"].ToString();
    SqlDbType sqlType = (SqlDbType)row["DATA_TYPE"];
    int? maxLength = row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? (int)row["CHARACTER_MAXIMUM_LENGTH"] : null;
    bool isNullable = row["IS_NULLABLE"].ToString().Equals("YES");
    
    // Get corresponding Access type, fallback to TEXT if no mapping exists
    if (!typeMap.TryGetValue(sqlType, out string accessType))
    {
        accessType = "TEXT";
    }
    
    // Add length for variable types (skip for MAX-length fields)
    if (maxLength.HasValue && maxLength.Value != -1 && (sqlType == SqlDbType.VarChar || sqlType == SqlDbType.NVarChar))
    {
        accessType = $"{accessType}({maxLength.Value})";
    }
    
    // Add nullable constraint if needed
    if (!isNullable)
    {
        accessType += " NOT NULL";
    }
    
    // Wrap column names in brackets to handle spaces/special characters
    columnDefinitions.Add($"[{colName}] {accessType}");
}

createTableSql.Append(string.Join(", ", columnDefinitions));
createTableSql.Append(")");

// Execute the CREATE TABLE command
using (OleDbConnection accessConn = new OleDbConnection(accessConnStr))
{
    accessConn.Open();
    using (OleDbCommand createCmd = new OleDbCommand(createTableSql.ToString(), accessConn))
    {
        createCmd.ExecuteNonQuery();
    }
    accessConn.Close();
}

Finally, pull the data from SQL Server and insert it into the newly created Access table. Using a SqlDataReader and parameterized queries keeps things efficient and avoids SQL injection risks:

// Fetch data from SQL Server
using (SqlConnection sqlConn = new SqlConnection(sqlConnStr))
{
    sqlConn.Open();
    string selectSql = $"SELECT * FROM [{sourceTable}]";
    using (SqlCommand selectCmd = new SqlCommand(selectSql, sqlConn))
    {
        using (SqlDataReader reader = selectCmd.ExecuteReader())
        {
            // Insert into Access
            using (OleDbConnection accessConn = new OleDbConnection(accessConnStr))
            {
                accessConn.Open();
                // Build INSERT command with parameter placeholders
                string columnList = string.Join(", ", reader.GetSchemaTable().Rows.Cast<DataRow>().Select(r => $"[{r["ColumnName"]}]"));
                string paramList = string.Join(", ", reader.GetSchemaTable().Rows.Cast<DataRow>().Select((_, i) => $"@p{i}"));
                string insertSql = $"INSERT INTO [{targetTable}] ({columnList}) VALUES ({paramList})";
                
                using (OleDbCommand insertCmd = new OleDbCommand(insertSql, accessConn))
                {
                    // Predefine parameters based on reader schema
                    foreach (DataRow row in reader.GetSchemaTable().Rows)
                    {
                        OleDbType oleDbType = MapSqlToOleDbType((SqlDbType)row["DataType"]);
                        insertCmd.Parameters.Add($"@p{insertCmd.Parameters.Count}", oleDbType);
                    }
                    
                    // Loop through reader and insert rows
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            insertCmd.Parameters[i].Value = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
                        }
                        insertCmd.ExecuteNonQuery();
                    }
                }
                accessConn.Close();
            }
        }
    }
    sqlConn.Close();
}

Dts.TaskResult = (int)ScriptResults.Success;
}

// Helper method to map SqlDbType to OleDbType for parameterization
private OleDbType MapSqlToOleDbType(SqlDbType sqlType)
{
    return sqlType switch
    {
        SqlDbType.Int => OleDbType.Integer,
        SqlDbType.VarChar => OleDbType.VarChar,
        SqlDbType.NVarChar => OleDbType.VarWChar,
        SqlDbType.DateTime => OleDbType.Date,
        SqlDbType.Decimal => OleDbType.Decimal,
        SqlDbType.Bit => OleDbType.Boolean,
        SqlDbType.BigInt => OleDbType.BigInt,
        SqlDbType.Float => OleDbType.Double,
        _ => OleDbType.VarChar
    };
}

• Use SSIS Variables: Replace hardcoded values with SSIS variables to make the script reusable across different tables and environments.
• Handle Large Datasets: For very large tables, batch inserts (e.g., commit every 1000 rows) to avoid memory issues.
• ACE Driver Compatibility: Ensure the correct ACE driver (32-bit vs 64-bit) is installed on your SSIS server, matching your runtime environment.
• Test Edge Cases: Add checks for special characters in column names, nullable fields, and rare data types to avoid errors.

内容的提问来源于stack exchange，提问作者sachi