TryParse SSIS Ignore Source Row

Question

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()))

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.

Edit: 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;
    }
}

this smells of a script component vs a derived column – KeithL Mar 15 '19 at 14:13 — KeithL, Mar 15 '19 at 14:13
Yes, definitely closer to what I am looking for. – Nyra Mar 15 '19 at 14:57 — Nyra, Mar 15 '19 at 14:57
You just missed the second date separator !! – Yahfoufi Mar 18 '19 at 14:30 — Yahfoufi, Mar 18 '19 at 14:30

KeithL · Accepted Answer · 2019-03-15T15:00:23.247

3

I'd use a script transformation:

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

Here's your code:

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;  }

edited Mar 15 '19 at 15:00

answered Mar 15 '19 at 14:27

KeithL

5,348
3
19
25

within the script task, I assume this should go in the Input0Buffer overrride.. This exposes the input (Row.[My Code] as above) and allows me to parse the input to a datetime variable. How then do I output to the convDate? Do I need to do something like Row.CreateNewOutPutRows(convDate)? That doesn't seem to make sense as I configured the output row in my setup. Inputs & Outputs -> Output 0 -> Output Columns -> convDate I cannot see this from within script component. – Nyra Mar 15 '19 at 14:56
The code has all the c# you need. however, you are gtting hung up in the SSIS GUI. On input/output tab select [My Code] for read. Also, on output add a new column to Output 0 called convDate. This will let you access the two columns from the Row – KeithL Mar 15 '19 at 14:59
Found answer.. Output0Buffer.convDate = dateValue; – Nyra Mar 15 '19 at 15:02
I don't agree with using Script component while you can achieve that in derived column **(especially when implementing very simple logic)**, the error was simple the OP forgot to add a separator between month and year. also he didn't arrange the datepart in `yyyy_MM-dd` format. – Hadi Mar 15 '19 at 20:37

Hadi · Answer 2 · 2019-03-18T20:46:02.140

2

It is better (from performance perspective) to avoid using Script Component while using simple expression

SSIS component vs C# script performance

The problem that you have in you derived column expression is that you are missing the second dash - and specify50 as length of string, try using the following expression:

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

Also it is preferable to use the universal date formatting YYYY-MM-DD while handling date values:

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

Make sure you have configured the error output to ignore failure:

Update 1

If you need to return the original string value use the following expression:

((dt_date)((dt_str,50,1252)datepart("yy",getdate()) + "-" + Substring([My Code], 3, 2) + "-" + Substring([My Code], 1, 2)) == (dt_date)((dt_str,50,1252)datepart("yy",getdate()) + "-" + Substring([My Code], 3, 2) + "-" + Substring([My Code], 1, 2))) 
? [My Code] 
: NULL(dt_str,50,1252)

edited Mar 18 '19 at 20:46

answered Mar 15 '19 at 20:30

Hadi

36,233
13
65
124

Ok. I agree this works but it kills me to chase errors on properties in gui – KeithL Mar 15 '19 at 20:56
@KeithL in this case the OP needs to implement a tryparse logic which doesn't raise error on parsing failure – Hadi Mar 15 '19 at 20:59
Agree. Both work. With mine. It attempts to parse. If fail writes nothing hence leaves column null. To me. Fixing 2 lines of code is easier than finding a property. – KeithL Mar 15 '19 at 21:02
@KeithL as you said both works. To be fair you will get my upvote :) – Hadi Mar 15 '19 at 21:04
I'm not arguing you are wrong. Just a preference for me when I have 100s of packages to maintain. – KeithL Mar 15 '19 at 21:06
@KeithL implementing custom code is not preferable when it can be achieved by ssis component – Hadi Mar 15 '19 at 21:08
Ditto ditto ditto – KeithL Mar 15 '19 at 21:08
I'm on the fence :3 For version migration and maintainability, I'd rather keep to basic SSIS components, but I'm not sure I can (and I do prefer C#). What I need to do is do the TryParse, but do something completely different if it's valid. The code is a string of numbers (a receipt) and from that there is a date parsed. I am trying to clean it before it gets into my stage table and SQL tries to parse it blowing me up. We ignore null, so the original column would just be passed null on error. This only allows me to pass the failed column right? – Nyra Mar 18 '19 at 14:50
@alykins if date is parsed successfully then the value is passed else a null value is passed – Hadi Mar 18 '19 at 15:12
But that's what I mean, I want to validate the parse, but then pass the serialized string un-parsed (knowing it will parse later). Essentially cleaning data from source. I have it going through a C# script task where I am able to make a validation class.. My biggest obstacle was understanding the syntax for the output buffer (figured out*). This solution does work great if I wanted to parse in the derived column; thank you for other concepts. – Nyra Mar 18 '19 at 16:37
@alykins it can be done with derived column. I will edit my answer in a while – Hadi Mar 18 '19 at 17:11

TryParse SSIS Ignore Source Row

2 Answers2

Update 1

Linked

Related