SSIS脚本任务C#导出SQL Server数据到文本文件格式问题求助

核心修改点 #

• 所有表头、字段值输出时统一增加双引号包裹
• 输出字段时自动判断数据类型，日期类型字段统一格式化为yyyy-MM-dd格式，仅保留日期部分
• 空值场景统一输出""，保证格式一致性

完整修改后代码 #

// Declare Variables
string DestinationFolder = Dts.Variables["User::Target_FilePath"].Value.ToString();
string QueryStage = Dts.Variables["User::Query_Stage"].Value.ToString();
//string TableName = Dts.Variables["User::TableName"].Value.ToString();
string FileName = Dts.Variables["User::OutputFileName"].Value.ToString();
string FileDelimiter = Dts.Variables["User::Target_FileDelim"].Value.ToString();
//string FileExtension = Dts.Variables["User::AC_Prefix"].Value.ToString();

//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_TEST_CONN"].AcquireConnection(Dts.Transaction) as SqlConnection);

// Read data from table or view to data table
string query = QueryStage;

SqlCommand cmd = new SqlCommand(query, myADONETConnection);
//myADONETConnection.Open();

DataTable d_table = new DataTable();
d_table.Load(cmd.ExecuteReader());
myADONETConnection.Close();

string FileFullPath = Path.Combine(DestinationFolder, FileName + ".txt"); // 替换拼接方式避免路径分隔符错误

StreamWriter sw = null;
sw = new StreamWriter(FileFullPath, false);

// Write the Header Row to File
int ColumnCount = d_table.Columns.Count;

for (int ic = 4; ic < ColumnCount; ic++)
{
    // 表头增加双引号包裹
    sw.Write($"\"{d_table.Columns[ic].ColumnName}\"");

    if (ic < ColumnCount - 1)
    {
        sw.Write(FileDelimiter);
    }
}

sw.Write(sw.NewLine);

// Write All Rows to the File
foreach (DataRow dr in d_table.Rows)
{
    for (int ir = 4; ir < ColumnCount; ir++)
    {
        sw.Write("\"");
        if (!Convert.IsDBNull(dr[ir]))
        {
            // 日期类型单独格式化，仅保留日期部分
            if (d_table.Columns[ir].DataType == typeof(DateTime))
            {
                sw.Write(((DateTime)dr[ir]).ToString("yyyy-MM-dd"));
            }
            else
            {
                sw.Write(dr[ir].ToString());
            }
        }
        sw.Write("\"");

        if (ir < ColumnCount - 1)
        {
            sw.Write(FileDelimiter);
        }
    }

    sw.Write(sw.NewLine);
}

sw.Close();

Dts.TaskResult = (int)ScriptResults.Success;

补充说明：代码中额外将路径拼接替换为Path.Combine方法，可避免不同环境下路径分隔符异常问题，不影响原有逻辑。

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