December 15, 2014 at 2:22 pm
I am having problems validating dates. I have an Excel spreadsheet that looks like this:
ID Data Submission
----------- ---------------
1 4/5/2014
2 7/5/2014
3 12//2014
4 12/9/2014
In SSIS I have it as an Excel Source, and trying to use Derived Column to catch for Invalid dates.
In my Expression, I am testing for Year([Date Submitted]). If it gives me a YEAR value, it should let the record pass. If not in my CONFIGURE ERROR OUTPUT, I am defining an ERROR to REDIRECT ROW.
I want it to catch ID 3 as an invalid date a redirect that Error. But for some reason, it is still passing all 4 records, instead of just 3 records that are valid and 1 record that is suppose to be invalid.
I am using a Union ALL to see my outputs with Data Viewer enabled and I am seeing all 4 records passed that would look like this:
ID Data Submission
----------- ---------------
1 4/5/2014
2 7/5/2014
3 NULL
4 12/9/2014
Any thoughts on what I am doing wrong?
December 15, 2014 at 2:32 pm
I don't like using the error output when I can remove the erroneous data myself. I would do it differently.
I would add a condition split in the data flow to remove records with invalid dates from the main stream of data. You can direct these bad records to a different output.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2014 at 2:57 pm
Alvin Ramard (12/15/2014)
I don't like using the error output when I can remove the erroneous data myself. I would do it differently.I would add a condition split in the data flow to remove records with invalid dates from the main stream of data. You can direct these bad records to a different output.
Do you have an example to check for NULL or bad dates in the condition?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply