c# - SSIS 自定义数据流组件 - 重定向错误行

Question

我创建了一个 SSIS 自定义数据流组件，该组件执行将日期从格式为 CYYMMDD 的 COBOL 大型机日期类型转换为 SQL Server 支持的格式 YYYYMMDD 的简单任务。存在错误行，因为传入的 COBOL 格式日期可能是无效日期(即 2-29-2015、4-31-2016、9-31-2010 等)。这些错误日期来自用户派生的输入字段，这些字段上没有日期掩码，我无法添加日期掩码，因为该应用程序属于第 3 方数据供应商。

我的问题:

自定义组件不会重定向错误行。我在 MSDN 上找到了一篇解释如何重定向错误行的帖子:

https://msdn.microsoft.com/en-us/library/ms136009.aspx

我使用此代码作为指南并对其进行了修改以满足我能够在多个选定的输入列上执行的需要(MS 的示例仅考虑一个输入列)。当我执行该过程时，出现以下两个错误:

[ConvertCobolDates [2]] 错误:System.ArgumentException:值不在预期范围内。 在 Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.DirectErrorRow(Int32 hRow，Int32 lOutputID，Int32 lErrorCode，Int32 lErrorColumn) 在 Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.DirectErrorRow(Int32 outputID，Int32 errorCode，Int32 errorColumn) 在 SSIS.Convert.CobolDate.DataFlow.ConvertCobolDateDataFlow.ProcessInput(Int32 inputID，PipelineBuffer 缓冲区) 在 Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 包装器、Int32 inputID、IDTSBuffer100 pDTSBuffer、IntPtr bufferWirePacket)[SSIS.Pipeline] 错误:SSIS 错误代码 DTS_E_PROCESSINPUTFAILED。组件“ConvertCobolDates”(2) 上的 ProcessInput 方法在处理输入“Input”(4) 时失败，错误代码为 0x80070057。标识的组件从 ProcessInput 方法返回错误。该错误特定于组件，但该错误是 fatal error ，将导致数据流任务停止运行。在此之前可能会发布错误消息，其中包含有关失败的更多信息。

此外，我不知道我是否缺少专门重定向输出的代码，或者错误处理是否未正确完成 - 它没有显示在“配置错误输出”屏幕中。非常感谢任何帮助! enter image description here

注意:我怀疑错误出在以下位置之一:ProvideComponentProperties 或 ProcessInput。

    public override void ProvideComponentProperties()
    {
        try
        {
            // Perform the base class' method
            base.ProvideComponentProperties();

            // Start out clean, remove anything put on by the base class
            base.RemoveAllInputsOutputsAndCustomProperties();

            // Set component information
            ComponentMetaData.Name = "ConvertCobolDates";
            ComponentMetaData.Description = "Data Flow task that converts COBOL date types into SQL Server Date types for each row flowing through the component.";
            ComponentMetaData.ContactInfo = "Contact Info.";
            ComponentMetaData.UsesDispositions = true; // As a rule, components should support error dispositions - they make it easier to troubleshoot problems with the data

            // Create input objects. This allows the custom component to have a 'Success' input data flow line
            IDTSInput100 input = ComponentMetaData.InputCollection.New();
            input.Name = "Input";
            input.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow; // Use RD_RedirectRow is ComponentMetaData.UsesDispositions = true. Otherwise, use RD_NotUsed
            input.ErrorOrTruncationOperation = "Either a bad date has been detected or an input column(s) has been selected that does not contain dates.";

            // Create output objects. This allows the custom component to have a 'Success' output data flow line
            IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
            output.Name = "Output";
            output.SynchronousInputID = input.ID; //Synchronous transformation
            output.ExclusionGroup = 1;

            // Create output objects. This allows the custom component to have a 'Error' output data flow line
            IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
            errorOutput.IsErrorOut = true;
            errorOutput.Name = "ErrorOutput";
            errorOutput.SynchronousInputID = input.ID;
            errorOutput.ExclusionGroup = 1;
        }
        catch (Exception ex)
        {
            bool bolCancel = false;
            ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, "", 0, out bolCancel);
            throw;
        }
    }

   public override void ProcessInput(int inputID, PipelineBuffer buffer)
    {
        IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);

        // This code assumes the component has two outputs, one the default,
        // the other the error output. If the intErrorOutputIndex returned from GetErrorOutputInfo
        // is 0, then the default output is the second output in the collection.
        int intDefaultOutputID = -1;
        int intErrorOutputID = -1;
        int intErrorOutputIndex = -1;
        int intErrorColumnIndex = -1;
        bool bolValidDate = false;

        GetErrorOutputInfo(ref intErrorOutputID, ref intErrorOutputIndex);

        if (intErrorOutputIndex == 0)
            intDefaultOutputID = ComponentMetaData.OutputCollection[1].ID;
        else
            intDefaultOutputID = ComponentMetaData.OutputCollection[0].ID;

        // Process each incoming row
        while (buffer.NextRow())
        {
            try
            {
                for (int i = 0; i < inputBufferColumnIndex.Length; i++)
                {
                    if (!buffer.IsNull(inputBufferColumnIndex[i]))
                    {
                        // Get the name of the current column that is being processed
                        string strColName = this.ComponentMetaData.InputCollection[0].InputColumnCollection[i].Name;

                        // Get the current row number that is being processed
                        int intCurRow = buffer.CurrentRow + 2;     // Buffer.CurrentRow is zero bounded and the first row is a header row, which is skipped. Adjust by two to account for this

                        // Ideally, your code should detect potential exceptions before they occur, rather
                        // than having a generic try/catch block such as this. However, because the error or truncation implementation is specific to each component,
                        // this sample focuses on actually directing the row, and not a single error or truncation.

                        // Get the ID of the PipelineBuffer column that may cause an error. This is required for redirecting error rows
                        intErrorColumnIndex = this.ComponentMetaData.InputCollection[0].InputColumnCollection[i].ID;

                        string strCobolDate = buffer.GetString(inputBufferColumnIndex[i]);
                        string strConvertedCobolDate = ConvertCobolDate(strCobolDate, strColName, intCurRow);
                        DateTime dtConvertedSQLDate;

                        // Validate that the date is correct. This detects bad dates (e.g., 2-30-2016, 4-31-2015, etc.) that are inputted from the user
                        // Throw an error if the date is bad
                        bolValidDate = DateTime.TryParse(strConvertedCobolDate, out dtConvertedSQLDate);
                        if (!bolValidDate)
                        {
                            // Validation failed, throw an exception and redirect the error row
                            throw new Exception();
                        }
                        else if (bolValidDate)
                        {
                            // validation passed. Direct the column back to its corresponding row within the pipeline buffer
                            buffer[inputBufferColumnIndex[i]] = dtConvertedSQLDate.ToShortDateString();
                        }
                    }
                }

            // Unless an exception occurs, direct the row to the default 
            buffer.DirectRow(intDefaultOutputID);
            }
            catch(Exception)
            {                     
                // Has the user specified to redirect the row?
                if (input.ErrorRowDisposition == DTSRowDisposition.RD_RedirectRow)
                {
                    // Yes, direct the row to the error output.
                    buffer.DirectErrorRow(intErrorOutputID, 0, intErrorColumnIndex);
                }
                else if (input.ErrorRowDisposition == DTSRowDisposition.RD_FailComponent || input.ErrorRowDisposition == DTSRowDisposition.RD_NotUsed)
                {
                    // No, the user specified to fail the component, or the error row disposition was not set.
                    throw new Exception("An error occurred, and the DTSRowDisposition is either not set, or is set to fail component.");
                }
                else
                {
                    // No, the user specified to ignore the failure so direct the row to the default output.
                    buffer.DirectRow(intDefaultOutputID);
                }
            }
        }
    }

Answer 1

经过一些艰苦的研究和 friend 的帮助，问题已经确定 - 传递给 DirectErrorRow 函数的 errorCode 0(在我之前发布的 MSDN 文章中指定)不正确 [它实际上是一个负数(在这种情况下:-1071628258)]。这是一个很难修复的错误，因为编译器在没有指定超出范围的参数和值的情况下输出一般性越界错误(见下文)。

• “System.ArgumentException:值不在预期范围内。” ... 消息被截断以减少帖子长度

我认为编译器错误指的是它无法转换的实际错误日期，因此我将所有时间都花在了 intErrorColumnIndex 上，MSDN 文章将其列为:

• //TODO:添加代码以包含 intErrorColumnIndex。

我假设 Microsoft 提供的错误代码 0 是正确的。凭直觉，我的 friend 说尝试检索实际的错误代码并且成功了!因此，errorCode 可能在负无穷大到 -1 之间的某处。微软关于定向错误行的 MSDN 文章需要更正。

我:1

微软:0

解决方法在catch block 中如下:

            catch(Exception)
            {                     
                // Has the user specified to redirect the row?
                if (input.ErrorRowDisposition == DTSRowDisposition.RD_RedirectRow)
                {
                    // Yes, get the error code
                    int DTS_E_ERRORTRIGGEREDREDIRECTION = -1;
                    unchecked
                    {
                        DTS_E_ERRORTRIGGEREDREDIRECTION = (int)0xC020401E;
                    }

                    // Direct the row to the error output
                    buffer.DirectErrorRow(intErrorOutputID, DTS_E_ERRORTRIGGEREDREDIRECTION, intErrorColumnIndex);
                }
                else if (input.ErrorRowDisposition == DTSRowDisposition.RD_FailComponent || input.ErrorRowDisposition == DTSRowDisposition.RD_NotUsed)
                {
                    // No, the user specified to fail the component, or the error row disposition was not set
                    throw new Exception("An error occurred, and the DTSRowDisposition is either not set or is set to fail component.");
                }
                else
                {
                    // No, the user specified to ignore the failure so direct the row to the default output
                    buffer.DirectRow(intDefaultOutputID);
                }
            }