Replace text in a imported file through SSIS


I am importing a flat file through SSIS and doing some processing and export it to SQL table. I have a few date fields in the file, I import them as text and eventually put them in the SQL table as a date field.

The problem I have is some of the dates in the date fields are sent to us as "9999-99-99", and thoes don't work, they just show up in my table as null, so what I would like to do is replace "9999-99-99" with a valid date like "2013-01-01".

So I was wondering if anyone had any suggestion on how to do this, without creating a staging table or changing the dates in the actual file.

Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
That would be a derived column task placed between the source and destination, that replaces the column, with an expression that goes something like this:
REPLACE(TheDateField, "9999-99-99","2013-01-01")

Open in new window

OpusJonesAuthor Commented:
I could swear I already tried that, but it worked.

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.