2

I have a OLEDB source with sql query. i am writing code to fetch data from a table. Also it has some conversions (varchar to datetime). When random data comes like 99999999, its not able to convert the value to datetime and it fails. Is it possible to handle such rows and redirect them to some error output?

Note - i tried error output option, but it doesnt works are the source query itself fails and entire package fails because of it

Gaurav Jain
  • 21
  • 1

1 Answers1

0

you can use try_convert in the query which will set to null when the conversion is not possible. Then you can handle the null dates in another component within the data flow if required.

example:

select try_convert(date, '20150101')
select try_convert(date, '99999999')
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • Thanks, it works.. but i wanted something like error handling at OLEDB source level for such scenarios. – Gaurav Jain Jun 01 '17 at 07:59
  • I don't think that's possible because the sql will fail before sending the rows to the pipeline. Alternatively you can read the table through the OLEDB source and then do the conversion with a data conversion component, which will allow you to segregate the incorrect rows. – Jayvee Jun 01 '17 at 09:00