Testing for Valid Date or Null Value -- Using Conditional Split
I have a Flat File Source with several columns of data that should be valid dates. Sometimes however they may either be NULL or not valid dates. I understand the expresstion ISNULL([ColumnName]) will test for the NULL condition. What is the syntax to test if the value is a valid date? It might be a string such as "baddate" or it could be a date that is too old. Could you provide an example of an expression that will test for both of these conditions? Thanks. Steven
October 31st, 2010 1:03am

Inside Conditional Split you can write an expression as: (DT_DBDATE)Date == (DT_DBDATE)Date. I have used following as the input for conditional split: select '2010-12-21' as Date Union select '2009-13-10' Union select '2010-09-32' Union select '2009/01-12' Union select Null and configured error output of conditional split for error and truncation as redirect row. Upon execution, 2010-12-10 and 2009/01-12 were treated as valid rows while rest 3 records were redirected as erroneous data. Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2010 1:16am

Thanks, but that was not quite what I was seeking. I don't want the error output to be the result of the test. I want to use the Conditional Split to create a number of outputs depending on the issue with the data. So I need a test where if the test is TRUE it means that the data is not a date. Such as ISDATE([FieldName]) == FALSE. But I don't see an ISDATE function to use. Another thing i have noticed is that the ISNULL([Date]) function is producing a TRUE result when the data in the field is a string such as "baddate". The metadata from the flat file seems to know this should be a date field because on the Flat File Output it shows the type as DT_DBTIMESTAMP. How did it know that? Is it looking all the way down to the OLE Destination table to see this as a smalldatetime column? Anyway, when it sees the "baddate" string it changes it to a NULL and passes it down the conditional pipe to the NULL Dates stream rather than to my default output of GOOD DATA. Any explanation of what is going on under the covers and how I can get the conditional split task to pass the string "baddata" through to my error table would be appreciated. Thanks. Steven
October 31st, 2010 1:49am

Hello Steven, Unfortunately, since there is no built in function like IsDate (exists in SQL Server) you would either need to - Test your date using a sophisticated expression, or Submit this date to SQL Server using IsDate to return the result, or finally Use a Script Task's VB engine to check your date (Datetime.TryParseExact). Personally I would go with the expression for greater flexibility, you please choose what you want and if you have questions do not hesitate to ask here.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2010 12:32pm

Hi, You can use Data conversion component and convert column into datetime, if conversion failed it means it’s not a valid date and you can either rejects those records or put some derived column and assign default dates. Gaurav Gupta
October 31st, 2010 4:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics