Hey there! Let’s walk through exactly how to build this C# Script Task for SSIS—since you’ve got over 1000 tables to copy, no manual column mapping needed, and a WHERE clause requirement, this approach will be fast and scalable.

We’ll use C# to dynamically fetch all tables from your source server (Server A), then use SqlBulkCopy (a high-performance .NET tool for bulk data transfers) to copy filtered data to Server B. Since your table structures are identical, we don’t need manual column mapping—SqlBulkCopy automatically matches columns by name.

First, create two OLE DB Connection Managers in your SSIS package:

• Name one SourceDB (points to Server A’s database)
• Name the other TargetDB (points to Server B’s database)
  Make sure the account running SSIS has read access to SourceDB and insert access to TargetDB.

1. Drag a Script Task onto your SSIS Control Flow panel.
2. Double-click to edit it:
   • Under ReadOnlyVariables, you can add a variable for your WHERE clause (e.g., User::FilterCondition) if you want to adjust the filter without editing code.
   • Select C# as the script language.
3. Click Edit Script to open the Visual Studio Tools for Applications (VSTA) editor.

Replace the default code with the following—every part is commented to explain what’s happening:

First, add required namespaces #

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

Main method (entry point for the script) #

public void Main()
{
    bool fireAgain = false;
    try
    {
        // 1. Get connection strings from SSIS Connection Managers
        string sourceConnStr = ((SqlConnection)Dts.Connections["SourceDB"].AcquireConnection(null)).ConnectionString;
        string targetConnStr = ((SqlConnection)Dts.Connections["TargetDB"].AcquireConnection(null)).ConnectionString;

        // 2. Define your WHERE clause (or pull from an SSIS variable: Dts.Variables["User::FilterCondition"].Value.ToString())
        string whereClause = "ModifiedDate > '2019-01-01'";

        // 3. Get all user tables from the source database (exclude system tables)
        List<string> tableNames = GetSourceTableList(sourceConnStr);

        // 4. Copy data for each table
        foreach (string tableName in tableNames)
        {
            CopyFilteredTableData(sourceConnStr, targetConnStr, tableName, whereClause);
            Dts.Events.FireInformation(0, "Table Copy Success", $"Finished copying table: {tableName}", string.Empty, 0, ref fireAgain);
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "Script Task Failed", $"Unexpected error: {ex.Message}", string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Helper method to fetch table names from the source #

private List<string> GetSourceTableList(string connectionString)
{
    List<string> tableList = new List<string>();

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        // Query system views to get only user-created tables (exclude system tables)
        string getTablesSql = @"
            SELECT TABLE_NAME 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE' 
              AND TABLE_SCHEMA = 'dbo' -- Adjust schema if needed
              -- Add TABLE_NAME NOT IN ('TableToSkip1', 'TableToSkip2') to exclude specific tables
        ";

        using (SqlCommand cmd = new SqlCommand(getTablesSql, conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    tableList.Add(reader["TABLE_NAME"].ToString());
                }
            }
        }
    }

    return tableList;
}

Helper method to copy filtered table data with SqlBulkCopy #

private void CopyFilteredTableData(string sourceConnStr, string targetConnStr, string tableName, string whereClause)
{
    using (SqlConnection sourceConn = new SqlConnection(sourceConnStr))
    {
        sourceConn.Open();
        // Fetch only rows matching the WHERE clause from the source table
        string selectSql = $"SELECT * FROM dbo.{tableName} WHERE {whereClause}";
        
        using (SqlCommand selectCmd = new SqlCommand(selectSql, sourceConn))
        {
            using (SqlDataReader dataReader = selectCmd.ExecuteReader())
            {
                // Use SqlBulkCopy for fast bulk insertion
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConnStr))
                {
                    bulkCopy.DestinationTableName = $"dbo.{tableName}";
                    // Auto-map columns (works because table structures are identical)
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        string colName = dataReader.GetName(i);
                        bulkCopy.ColumnMappings.Add(colName, colName);
                    }
                    // Adjust batch size based on your data size (10k-50k is a good starting point)
                    bulkCopy.BatchSize = 10000;
                    // Write the filtered data to the target table
                    bulkCopy.WriteToServer(dataReader);
                }
            }
        }
    }
}

Keep the default ScriptResults enum #

enum ScriptResults
{
    Success = 0,
    Failure = 1
}

• Flexible Filtering: Replace the hardcoded whereClause with an SSIS variable to adjust filters without editing code. Just add the variable to the Script Task’s ReadOnlyVariables and fetch it with Dts.Variables["User::FilterCondition"].Value.ToString().
• Error Handling: The try-catch block logs errors, but you can modify it to skip failed tables (instead of failing the whole task) by moving the try-catch inside the table loop.
• Performance: Tweak the BatchSize based on your data—larger batches are faster but use more memory. For very large tables, consider adding row count checks to skip empty tables.
• Exclude Tables: Add a TABLE_NAME NOT IN (...) clause in the GetSourceTableList query to skip any tables you don’t need to copy.

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