c# - 如何调试脚本作为 SSIS 项目的一部分？

Question

我有两个数据库。源数据库: http://sdrv.ms/VkX8tj

和星级数据库: http://sdrv.ms/12S7Zkc

我正在使用 SSIS 项目将数据从 1 号传输到 2 号:sdrv.ms/YDUvU4

(无法发布第三个链接，不便之处，敬请谅解)

如您所见，我的脚本中有一个错误。它显示“该列具有空值”。或准确地说(通过弹出窗口):

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.CheckStatusAndNull(Int32 columnIndex) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.GetDecimal(Int32 columnIndex) at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

和(通过调试器的输出):

Error: 0xC0047062 at Data Flow Task, Script Component [337]: Microsoft.SqlServer.Dts.Pipeline.ColumnIsNullException: The column has a null value. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket) Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Script Component" (337) failed with error code 0x80131600 while processing input "Input 0" (347). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

除了填写这个函数外，我的脚本与模板完全一样:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    decimal WorkerWage = Row.Amount * Row.HourWage * Row.ProductionHours;
    Row.WorkerWage = WorkerWage;

    decimal TotalProductionCost = Row.Amount * Row.ProductionHours * (Row.ProductionCost + Row.HourCost) + WorkerWage;
    Row.TotalProductionCost = TotalProductionCost;

    decimal StandardPrice = TotalProductionCost * (1 + Row.Profit/100);
    Row.StandardPrice = StandardPrice;

    decimal TotalDiscount = StandardPrice * (Row.Discount / 100);
    Row.TotalDiscount = TotalDiscount;

    decimal TotalPrice = StandardPrice - TotalDiscount;
    Row.TotalPrice = TotalPrice;

    decimal TotalShippingCost = Row.ShippingCost + TotalPrice * (Row.ShippingTax / 100);
    Row.TotalShippingCost = TotalShippingCost;

    decimal RealProfit = TotalPrice - TotalProductionCost;
    Row.RealProfit = RealProfit;

    decimal TraderMargin = RealProfit * (Row.ProfitMargin / 100);
    Row.TraderMargin = TraderMargin;
}

“该列有一个空值”意味着什么，但它是输入列还是输出列，究竟是哪一个？如何解决？

顺便说一下，请不要关注这个项目基于内容的值(value)，因为它已经完成(好吧，我希望会完成)只是出于教育目的。

Answer 1

查看您的架构，似乎没有任何列允许 NULL。因此，源列中的值为 NULL 是不可能的。

您可以将失败的行重定向到不同的目标并查看它以识别有问题的行。

拉吉