December 14, 2011 at 12:00 pm
Hi All
Relative newbie to SSIS so forgive me if this is a simple one
I am trying to test and count the number of date fields in a record that have a date value.
I have tried the following in a conditional split
ISNULL(Date1)? 0:1 + ISNULL(Date2)? 0:1 + ISNULL(Date3)? 0:1 > 1
This obviously fails miserably since it expect a boolean not a number. any suggestions to achieve the result?
December 14, 2011 at 8:25 pm
My workaround is to use isdate in the data source SQL to create another field that contains 0 or 1 and pass them down through the various unions until they are needed. In my particular case there are 18 date fields that need to be part of this logic.. so I now have 36 fields instead of 18. Kinda messy.
Surely SSIS can do this?
December 14, 2011 at 9:03 pm
derived column instead? use what you had previously ISNULL(Date1)? 0:1 + ISNULL(Date2)? 0:1 + ISNULL(Date3)? 0:1 + ...... and the resultant value is the count of not null date fields??
Steve.
December 19, 2011 at 11:24 am
Thank you!
Just tested, and that WILL work in derived column. Matter of fact it will work in the conditional split too.. However:
You need to place brackets around the ISNULL(Date1? 0:1) So the syntax would be:
(ISNULL(Date1? 0:1)) + (ISNULL(Date2? 0:1)) + (ISNULL(Date3? 0:1)) etc..
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply