locked
Mapping Data Flows Error Handling RRS feed

  • Question

  • Hi,

    I have a question regarding how mapping data flows handle potential errors. As an example, let's say I want to load the contents of a file (.csv) into an azure sql database. That file contains 3 columns, all of them integers defined as such in the projection tab of the source component in the mapping data flow.

    Source Projection

    These 3 columns map with the corresponding columns in the database (also defined as int). However, if one of those columns contains a string ('abc' for example), what I've noticed is that the record still gets loaded but the value of that column just gets a NULL value.

    I was expecting to have something like the copy activity has where we can log and skip incompatible rows in case of these type of data mismatches. Am I missing something or is this simply not possible in mapping data flows?

    Thanks

    Pedro Fiadeiro

    Thursday, February 20, 2020 12:08 PM

Answers

  • Hi Pedro,

    Thanks for your query. You could try implementing Data Flow Error Row Handling concept using Conditional Split in your Data Flow. 

    Please refer to this doc to explore more about error row in handling in Data Flow : Data Flow Error Row Handling

    I have tried below condition in conditional split with a sample data and was able to achieve this. You could try tweaking the condition based on your requirement.

    !isNull(greaterOrEqual((toInteger(IntegerValue)),0))
     

    Or 

    !isNull((toInteger(IntegerValue)))


    Source Data Preview:



    Condition used in Conditional Split:

     

    Data Preview of Good Rows


    Data Preview of Bad/Error rows: 


    Hope this helps. Let us know how it goes.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.



    Friday, February 21, 2020 3:14 PM

All replies

  • Hi Pedro,

    Thanks for your query. You could try implementing Data Flow Error Row Handling concept using Conditional Split in your Data Flow. 

    Please refer to this doc to explore more about error row in handling in Data Flow : Data Flow Error Row Handling

    I have tried below condition in conditional split with a sample data and was able to achieve this. You could try tweaking the condition based on your requirement.

    !isNull(greaterOrEqual((toInteger(IntegerValue)),0))
     

    Or 

    !isNull((toInteger(IntegerValue)))


    Source Data Preview:



    Condition used in Conditional Split:

     

    Data Preview of Good Rows


    Data Preview of Bad/Error rows: 


    Hope this helps. Let us know how it goes.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.



    Friday, February 21, 2020 3:14 PM
  • Hi KranthiPakala-MSFT

    Appreciate your reply and I understand your logic. It would have to be a bit more complicated than that if the column allows NULL values but I see the point.

    Looking at the example you sent, why does data flow fail with truncation errors but doesn't fail with incompatible datatypes? I can build a logic that builds with potential incompatible datatypes but would rather have that process happen automatically and the job just fail or skip the incorrect rows. I also suppose that may be other kind of errors, such as this one, that may be ignored and records still inserted.

    Do you know if in future developments this will be handled in a different way?

    Thanks,

    Pedro Fiadeiro

    Monday, February 24, 2020 8:56 AM
  • Hi Pedro Fiadeiro,

    I totally agree with your point. I have reached out to internal team to get further info about your query. Will keep you posted once I have a response from the team. 

    I the mean time, I would suggest you to please share your idea/suggestion in Azure Data Factory feedback forum. 

    ADF User voice forum: https://feedback.azure.com/forums/270578-data-factory/


    All the feedback shared in this forum are monitored and reviewed by ADF engineering team and will take necessary action based on the priority. 

    Once you have created the feedback about the feature request, please share the link here, as it would help other community members to up-vote and/or comment on your suggestion which in-turn will help ADF engineering team to prioritize the feature requests.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Monday, February 24, 2020 7:52 PM
  • Hi Pedro Fiadeiro,

    Update: Adding error row handling natively in ADF data flows is in the engineering plan for upcoming implementations, hoping to have something soon this year but I don not have a definite ETA at the moment. If I have any further update regarding this, I will keep posted here.

    But I would recommend you to please open a feedback thread and post the link here as it would help other community members with similar idea to up-vote your idea/suggestion, which in-turn will help to increase the priority of feature work implementation. 

    Hope the above info helps.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.



    Friday, February 28, 2020 1:01 AM
  • Hi Hi Pedro Fiadeiro,

    Just checking to see if the above information was helpful? In case if you have created a user voice thread, I would request you to please post the link here, as it will give an opportunity to other community members to upvote/comment your feedback.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, March 3, 2020 12:19 AM
  • Hi KranthiPakala-MSFT,

    Apologies but only now did I have time to create that feedback on the feedback forum. Here is the link:

    Feedback Forum Idea

    Thanks for all your feedback on this issue

    Wednesday, March 4, 2020 11:51 AM
  • Hi Pedro Fiadeiro,

    Thanks much creating the feedback link and sharing it here. 

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, March 4, 2020 8:26 PM