jeudi 7 juillet 2016

Need to take care the junk values while converting varchar to datetime using SSIS

I am a newbie in SSIS and in my SSIS package, I have a column that has data in ddMMyyyy format which I need to convert to datetime which I am able to do through Derived Column Transformation but at times, I get junk values like ABC, NR, etc in that column and due to which my package fails.

Is there any method I can take care of such values and show them as NULL in my destination table? I have tried using it in Derived Column Expression but the package execution fails with "The value could not be converted because of a potential loss of data."

For example: My column name is SampleDate and the expression I am using to concert to datetime is as follows:

(DT_DBTIMESTAMP)(SUBSTRING(SampleDate,3,2) + "/" + (LEFT(SampleDate,2) + "/" + RIGHT(SampleDate,4)))

Aucun commentaire:

Enregistrer un commentaire