Hi 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
+2
A:
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...
klabranche
2009-08-06 01:37:36
A:
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.)
HLGEM
2009-08-06 13:31:46
How do validate date in Dataconversion task?
rmdussa
2009-08-12 00:05:53
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.
HLGEM
2009-08-12 13:15:46
First place need to how to validate date? My main aim to find given value whether its valid date or not
rmdussa
2009-08-12 22:32:25
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.
HLGEM
2009-08-13 13:02:43
+1
A:
See also http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html > "IsDate workaround" for a technique that can be adapted
onupdatecascade
2009-08-11 20:35:14
@onupdatecascade I get `Server Error in '/wiki' Application. Security Exception` when I go to that site.
Raj More
2010-08-16 15:12:13