TryParse SSIS 忽略源行

2021-12-30 00:00:00 etl date sql-server ssis derived-column

我有一个序列化的代码，在这段代码中，有一些数值在解析时代表一个日期.

I have a serialized code, and from within this code there are numeric values which when parsed represent a date. 

For example, 011756420176654 
*Note* array index may be off
Substring(1,2) = 01
Substring(3,2) = 17

我试图忽略该行，而不替换原始行.我有一个派生列，并且正在列中执行此操作.

I'm trying to ignore the row, without replacing the original row. I have a derived column, and am doing this in the column.

(dt_date)(Substring([My Code], 1, 2) + "-" + Substring([My Code], 3, 2) + (dt_str,10,1252)datepart("year",getdate()))

我的目的是将我的错误输出配置为在派生列中的TryParse"逻辑失败时忽略 [My Code] 字段.我知道如果我正在传递派生列，那么在配置上选择忽略将传递空值，但问题是我试图(错误时)忽略源行并将其作为空值传递(即 [我的代码]).

My intention here is to configure my error output to ignore the [My Code] field if the "TryParse"-logic in the derived column fails. I know if I were passing the derived column, that selecting ignore on the configuration will pass null, but the problem is I'm trying to (on error) ignore the source row and pass that as null (ie [My Code]).

一旦这个命中是数据库，另一个进程就会使用它并尝试解析日期.它不会在空值上失败，所以我想在允许记录通过或将其设置为空之前验证本质上是日期"逻辑.

Once this hit's the database, another process consumes it and attempts to parse the dates. It will not fail on null values, so I want to validate that essentially "is date"-logic before allowing the record through, or setting it to null.

根据基思的解决方案，我来到了这个.我在访问输出缓冲区时遇到了问题，但是在对语法进行了一些 MSDN 之后，我想出了以下完美的方法.

Per Keith's solution, I came to this. I was having issues accessing the output buffer, but after some MSDN on syntax I came up with the following which works perfectly. 

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        DateTime dateValue;
        string test = Row.ReceiptCode.Substring(0, 2) + "/" + Row.ReceiptCode.Substring(2, 2) + "/" + DateTime.Now.Year.ToString();

        if (DateTime.TryParse(test, out dateValue) && Row.ReceiptCode.Length ==16)
    {

        Output0Buffer.AddRow();

        Output0Buffer.EndDate = Row.EndDate;
        Output0Buffer.Q10 = Row.Q10;
        Output0Buffer.Q8 = Row.Q8;

        Output0Buffer.ValidatedReceipt = Row.ReceiptCode;
    }
    else 
    {
        Output1Buffer.AddRow();

        Output1Buffer.EndDate = Row.EndDate;
        Output1Buffer.Q10 = Row.Q10;
        Output1Buffer.Q8 = Row.Q8;
        Output1Buffer.Error = Row.ReceiptCode;
    }
}

推荐答案

我会使用脚本转换:

添加一个输出列 (convDate) 并检查 [My Code] 为已读:

Add an ouput column (convDate) and check [My Code] as read:

这是您的代码:

string test = Row.[My Code].Substring(1,2) + "/" + Row.[My Code].Substring(3,2)+"/" + DateTime.Now.Year.ToString();

if (DateTime.TryParse(test, out dateValue))
{Row.convDate = dateValue;  }

相关文章