Only inserting valid dates or null values.

Question

I am using an ssis package to migrate data from access to sql server. One of my fields in my access table contains dates, however not all are valid (typing errors etc) and a lot are null values. I want to only bring through the records with either a null value or a valid date which will go into a datetime field on my SQL table. The field is read as a dt_date.

Answer 1

Add a data conversion transform to your dataflow, and convert the column to DT_DBTIMESTAMP. If the value isn't valid, it will redirect the row to the error output, which you could connect to a Flat File Destination. That way, you can review and correct the bad records.

---

John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 2

Either you have to write some custom logic to filter out invalid date or try to use a derived column to create a new date column using the date values coming from source. Confgiure the error op of derived column to redirect row. This will filter all the invalid dates. As far as NULL values are concerned, you can replace them with some default value so that they are not filtered in derived column. So your expression would be:

ISNULL([Datefromsource])? (DT_DBDATE)"2100-12-12" : (DT_DBDATE)[Datefromsource]

---

Nitesh Rai- Please mark the post as answered if it answers your question