how to drop bad dates from excel file

Hi, what is the best way to drop a bad date within a ssis pkg.  I load data from an excel file into a sql server but sometimes the data is dirty, like a date field will come in ##### or 0222-01-01 00:00:00.000.  For these rows I want to load into tbl and the good rows into another tbl.  
I tried using the derived column task to make sure the data is actually a date, but get the following error.
Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s).        
Who is Participating?
vdr1620Connect With a Mentor Commented:
Are you getting the error at the source or at the Derived column? What expression are you using in the Derived Column...

I Think it is enough to check the year pattern of the Date column using a range like [2][0][0-9][0-9] Or [1-9][0][0-9][0-9] or use Year(Date column) >= 2011
which will eliminate the bad records , you can also check for # using the Codepoint function in the expression
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.