SSIS通过Web API批量上传数据时如何定位错误数据？

我有一个SSIS包，流程是通过Post请求将SQL查询获取的数千行数据转换为竖线分隔字符串，存入用户变量User::RequestBody，再在Script Task中通过WebClient.UploadString发送至外部公司的Web API。当前问题是：数千行数据中只要存在一条错误数据，整个API请求就会被拒绝（返回400 Bad Request），手动定位错误数据耗时极长。目前已将请求响应记录到SSMS表中，希望找到简单定位错误数据的方法，比如考虑逐条发送并标记错误记录。

现有Script Task代码 #

public void Main()
{
    string username = Convert.ToString(Dts.Variables["$Project::DestinationUserName"].Value);
    string password = Convert.ToString(Dts.Variables["$Project::DestinationPassword"].GetSensitiveValue());
    Uri DestinationURI = new Uri((string)Dts.Variables["$Project::DestinationSite"].Value);
    string stringToSend = (string)Dts.Variables["User::RequestBody"].Value;
    if (stringToSend != "")
    {
        string result;
        using (WebClient webClient = new WebClient())
        {
            var bytes = Encoding.UTF8.GetBytes(username + ":" + password);
            var auth = "Basic " + Convert.ToBase64String(bytes);

            //NetworkCredential myCreds = new NetworkCredential(username, password);
            //webClient.Credentials = myCreds;
            webClient.Headers[HttpRequestHeader.Authorization] = auth; 
            webClient.Headers[HttpRequestHeader.ContentType] = "text/plain"; 
            ServicePointManager.SecurityProtocol = ServicePointManager.SecurityProtocol | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; // beyond the standard SSL3 and TLS
            try
            {
                result = webClient.UploadString(DestinationURI, stringToSend); 
            }
            catch (WebException ex)
            {
                Dts.Events.FireError(0, "", "UnableToSendData: " + ex.Message.ToString() + ex.StackTrace, string.Empty, 0);
                return;
            }
        }
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

参考数据示例（每行以\n分隔，日常发送约9000条） #

"123456|123456|TestUser1|Adam|Mphil/PhD History (PT) Year|3|Department of History||P||||XK|Information not sought|||123456|123456@gmail.com|||||V1ZR-Y||HISs|H|M|1998-05-21||PGR|||0777XXXXXX|||2021-09-19|2022-09-10| |Test NOK: 58 Wiltshire Ave, HA8 5DG: 0208XXXXXX|||||\n678910|678910|TestUser2|Smith|Mphil/PhD Near and Middle East (PT) Year|12|Other Middle East||P||||XK|No known disability|||678910|678910@hotmail.com|||||T6ZT/D-Y||OMEs|H|M|1990-03-15||PGR|||079XXXXXXXX|||2022-09-26|2023-09-25| |Test NOK2: 45 Britannia Street, MK11 2ZQ: 0207XXXXXX|||||\n"

解决方案 #

方案1：逐条发送并记录错误行 #

修改Script Task代码，将批量数据拆分为单行，逐条发送请求，失败时将错误行内容和异常信息写入SSMS表，直接定位问题数据。

修改后的代码示例：

using System.Data.SqlClient;

public void Main()
{
    string username = Convert.ToString(Dts.Variables["$Project::DestinationUserName"].Value);
    string password = Convert.ToString(Dts.Variables["$Project::DestinationPassword"].GetSensitiveValue());
    Uri DestinationURI = new Uri((string)Dts.Variables["$Project::DestinationSite"].Value);
    string stringToSend = (string)Dts.Variables["User::RequestBody"].Value;
    // 拆分数据为单行（过滤空行）
    string[] rows = stringToSend.Split(new[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
    
    if (rows.Length > 0)
    {
        // 复用WebClient实例提升效率
        using (WebClient webClient = new WebClient())
        {
            var bytes = Encoding.UTF8.GetBytes(username + ":" + password);
            var auth = "Basic " + Convert.ToBase64String(bytes);
            webClient.Headers[HttpRequestHeader.Authorization] = auth; 
            webClient.Headers[HttpRequestHeader.ContentType] = "text/plain"; 
            ServicePointManager.SecurityProtocol = ServicePointManager.SecurityProtocol | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

            // 循环发送每一行
            foreach (string row in rows)
            {
                if (string.IsNullOrWhiteSpace(row)) continue;
                
                try
                {
                    string result = webClient.UploadString(DestinationURI, row);
                    // 可选：记录成功行
                    LogToSSMS(row, "Success", string.Empty);
                }
                catch (WebException ex)
                {
                    string errorMsg = $"Error: {ex.Message}\nStack Trace: {ex.StackTrace}";
                    // 记录错误行到SSMS表
                    LogToSSMS(row, "Failed", errorMsg);
                    // 可选：触发错误事件提醒
                    // Dts.Events.FireError(0, "", $"Failed row: {row}\n{errorMsg}", string.Empty, 0);
                }
            }
        }
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

// 辅助方法：写入日志到SSMS表
private void LogToSSMS(string rowData, string status, string errorMessage)
{
    // 替换为你的SSMS连接字符串（建议用项目变量存储）
    string connectionString = Convert.ToString(Dts.Variables["$Project::SSMSConnectionString"].Value);
    string insertSql = @"
        INSERT INTO ApiRequestLogs (RowData, Status, ErrorMessage, CreatedTime)
        VALUES (@RowData, @Status, @ErrorMessage, GETDATE())
    ";
    
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(insertSql, conn))
        {
            cmd.Parameters.AddWithValue("@RowData", rowData);
            cmd.Parameters.AddWithValue("@Status", status);
            cmd.Parameters.AddWithValue("@ErrorMessage", errorMessage ?? string.Empty);
            cmd.ExecuteNonQuery();
        }
    }
}

方案2：二分法批量排查（快速缩小范围） #

如果逐条发送效率较低，可通过二分法拆分数据批量发送，快速定位错误批次：

1. 将RequestBody拆分为2个批次（比如前4500行和后4500行）
2. 分别发送两个批次，找到返回400的批次
3. 对有错误的批次继续拆分，重复步骤直到定位到单行

方案3：提前数据校验 #

在生成RequestBody前，添加数据校验逻辑，提前过滤不符合规则的行：

• 检查每行的字段数量是否符合API要求（按竖线拆分后字段数是否一致）
• 校验日期格式、邮箱格式等字段合法性
• 检查字段中是否包含API不允许的特殊字符（比如未转义的竖线、控制字符等）

可在SQL查询阶段添加校验，或在SSIS的Data Flow中用派生列、条件拆分组件过滤错误行。

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