SSIS Script Task C#处理大数据内存不足问题求解

你遇到的核心问题是一次性将数百万条数据加载到DataTable导致内存溢出，解决思路是放弃全量加载，改用SqlDataReader逐行读取并写入文件，这样内存里始终只保留当前处理的一行数据，内存占用会大幅降低。以下是重构后的完整代码和关键说明：

关键优化点 #

• 用SqlDataReader替代DataTable，仅在内存中保留单条记录
• 使用using语句自动管理连接、命令、读取器和文件流的资源释放，避免内存泄漏
• 先通过读取器获取列信息生成表头，无需依赖DataTable
• 逐行处理数据并即时写入文件，避免缓存大量数据

重构后的代码 #

#region Namespaces
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.Dts.Runtime;
#endregion

namespace ST_35510fa90f9946dd823e277e917a3dc0
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            try
            {
                // 读取SSIS变量
                string destinationFolder = Dts.Variables["User::TARGET_FILE_PATH"].Value.ToString();
                string queryStage = Dts.Variables["User::QUERY_STAGE"].Value.ToString();
                string fileName = Dts.Variables["User::TARGET_FILE_NAME"].Value.ToString();
                string fileDelimiter = Dts.Variables["User::TARGET_FILE_DELIM"].Value.ToString();
                string fileFullPath = Path.Combine(destinationFolder, $"{fileName}.txt");

                // 使用using自动管理连接资源
                using (SqlConnection connection = (SqlConnection)Dts.Connections["ADO_MED_AFF_BIO_PROVIDER"].AcquireConnection(Dts.Transaction))
                using (SqlCommand cmd = new SqlCommand(queryStage, connection))
                using (SqlDataReader reader = cmd.ExecuteReader())
                using (StreamWriter sw = new StreamWriter(fileFullPath, false))
                {
                    // 写入表头（跳过前2列，和原逻辑一致）
                    int columnCount = reader.FieldCount;
                    for (int ic = 2; ic < columnCount; ic++)
                    {
                        sw.Write($"\"{reader.GetName(ic)}\"");
                        if (ic < columnCount - 1)
                        {
                            sw.Write(fileDelimiter);
                        }
                    }
                    sw.WriteLine();

                    // 逐行读取并写入文件
                    while (reader.Read())
                    {
                        for (int ir = 2; ir < columnCount; ir++)
                        {
                            if (!reader.IsDBNull(ir))
                            {
                                if (reader.GetFieldType(ir) == typeof(DateTime))
                                {
                                    DateTime dt = reader.GetDateTime(ir);
                                    sw.Write($"\"{dt.ToString("yyyy-MM-dd")}\"");
                                }
                                else
                                {
                                    sw.Write($"\"{reader[ir].ToString()}\"");
                                }
                            }
                            else
                            {
                                // 空值写入空引号，保持输出格式一致
                                sw.Write("\"\"");
                            }

                            if (ir < columnCount - 1)
                            {
                                sw.Write(fileDelimiter);
                            }
                        }
                        sw.WriteLine();
                    }
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                // 改用SSIS日志记录错误，适配非交互式执行环境
                Dts.Events.FireError(0, "Script Task Error", ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

额外说明 #

1. 资源管理：所有实现IDisposable的对象（连接、命令、读取器、流）都用using包裹，代码块结束时自动释放资源，避免内存泄漏
2. 错误处理优化：原代码的MessageBox在非交互式SSIS环境中无法显示，改为Dts.Events.FireError将错误记录到SSIS日志，符合规范
3. 空值处理：补充空值场景的输出逻辑，写入空引号保证格式一致性，可根据需求调整
4. 性能表现：逐行处理的性能与原方案接近，但内存占用从数百MB/GB降至几MB，完全支持数百万条记录的处理

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