Mapping data flow format date exception

Question

In Data factory mapping data flow, I am looking for replacement of TRY_CAST() in SQL server.

I am using ToDate() function to cast my date, however, the column also has junk data in between which cannot be converted to date.

I want to cast to date, only if matches the given date format, otherwise insert/make it NULL.

For example, try exception handling for the date format.

ToDate('2020-01-01') -> '2020-01-01' should give results

ToDate('63716363') -> Null

I am getting Arithmetic data flow error converting timestamp to date.

How to resolve this issue ?

Answer 1

Hi @Vaibhav Palve ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

We can achieve your requirement using iif() function.

Use below expression, to try to type cast value to date. If dataflow unable to type cast junk data then it will return null. Hence here, I am trying to see if toDate() function giving null or not and based on that trying to load correct date or empty value.
So, in my output all correct values will stay and junk values gets replace by empty.

In sink transformation you can write post query to replace all empty values with null in target table.

Hope this will help. Please let us know if any further queries. Thank you.

--------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators