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

Solution 1

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...

Solution 2

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.

Solution 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.)

Solution 4

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

View more solutions

Comments

[rmdussa]

How do validate date in Dataconversion task?

[dburges]

Click the button to configure error output and selct redirect row. Then connect the failure line to whatever tasks you want to perform if the record doesn;t convert to a date. Typically, I move errors of these types to a table where I can examine the rows and see what the problem is. That way I can write code to fix the problems or send the data back to the source asking them to fix or decide to null out the filed or whatever needs to be done. I may also fail the import if I find some types (or percentages) of bad data.

[rmdussa]

First place need to how to validate date? My main aim to find given value whether its valid date or not

[dburges]

If it won't convert to a date it will go to the failure path and you can decide what to do with it and add tasks to handle what you want to do with invalid dates. All the other records go along the success path and can follow the rest of your process as normal.

[Raj More]

I get Server Error in '/wiki' Application. Security Exception when I go to that site.

[onupdatecascade]

That's a shame - looks like the site is broken

[Rich]

this isn't the question, unfortunately. It is how do you test whether it is a date, not whether the date has a particular value.