Dealing with BAD Data in SSIS

[robboll]

I have a simple table with five columns. One of the column is a
date.

When I import the data into a datetime field I get the error: An OLE
DB error has occurred. Error code: 0x80004005. Description:
"Invalid character value for cast specification".

As it turns out the error occurs when it hits a date with this value
"00/00/0000"

Can anyone suggest a method of dealing with this issue?

Thank you!

RBollinger

[robboll]

Anyone familiar with RegexClean? I just imported the add-in
transformations to take care of these type of data issues.

[Gorm Braarvig]

to solve this in SSIS
- import as strings
- cast to date and redirect error rows

there are ways to fix this in SQL (the source statement) if you need better
performance, but easier is usually better...

hope this helps
Gorm

Post by robboll
I have a simple table with five columns. One of the column is a
date.
When I import the data into a datetime field I get the error: An OLE
"Invalid character value for cast specification".
As it turns out the error occurs when it hits a date with this value
"00/00/0000"
Can anyone suggest a method of dealing with this issue?
Thank you!
RBollinger

[Ryan.Guan(管悦)]

did you use cast in the sql query ?
Please check the data type.
or you can give a default value (TO_DATE()) for this column.

Post by robboll
I have a simple table with five columns. One of the column is a
date.
When I import the data into a datetime field I get the error: An OLE
"Invalid character value for cast specification".
As it turns out the error occurs when it hits a date with this value
"00/00/0000"
Can anyone suggest a method of dealing with this issue?
Thank you!
RBollinger