isdate function in ssis derived component

Question

Is there any way to check Date(like isDate function in TSQL) column in SSIS package derived column expression after extraction from Sourcefile before loading to target dtabase?

Thanks

Answer 1

You can check whether your variable has a date or not using a conditional statement like this:

testDateVariable?true:false

For example, if date > 2 then it is true (and put the date, or format the date as you wish). If it is false, put null (you replace true with the date format and false with null).

All this is in a drived column in SSIS.

Answer 2

See also http://www.sqlis.com/sqlis/post/Expression-Date-Functions.aspx > "IsDate workaround" for a technique that can be adapted

Answer 3

There is a a data conversion task you can drop in. Then redirect the rows as needed, either failing the import entirely or redircting the rows that don't work.

Or you could try a conditional split wher eyou cast the field to a date data type and then send the failures along another path (either deleting the records or nulling out the field would be the common action.)

Answer 4

there is no built in function but you can run a script task and use vb.net code to check if the column is a date and operate on it as you wish...

Answer 5

I had a similar issue. I had a date/time in a text file, but the field had a dash between the date and the time. I created a derived column to do a replace on the dash:

REPLACE([TimeField], "- ", "")

I then added a convert column to convert the field to a date. I chose to ignore errors. I then added another Derived Column to check if the converted field was NULL, indicating that it could not convert the value. If it was, I had it set to the current date.