Forum Discussion

jonesb321's avatar
jonesb321
Copper Contributor
Aug 14, 2020

Validation Errors - Stuck in an endless cycle

This has become a very serious frustration.  I've met with it once before, and I believed it to have been caused by branches containing Select or Derived columns with the same names.  I'm hoping someone can offer some insight to the problem, or if this is purely a defect in the UX.  I'm convinced I am doing something incorrectly, but can't for the life of me find the root of the problem.

 

I start with a completely valid data flow, make no changes, yet still get a serious of validation errors.  The solution to these errors is to simply re-select the values from the drop-down (which are still highlighted).  Although, I've also seen it clear the exception simply by clicking on the exception itself.

 

In my latest scenario, I have a JSON document that has 3 different nested arrays of objects:

  • Array1 - Just a simple array of objects
  • Array2 - An array of objects having its own nested array
  • Array3 - An array of objects containing its own set of nested arrays, similar to both Array1 and Array2

The JSON object delivered via the source needs to be cleansed and transformed into a different JSON object before the sink.

 

In my approach, I am branching the source so that Arrays 1, 2, and 3 can each go through the process of being flattened, cleansed, transformed, and collected prior to being joined back to the main branch.

 

    Have I over complicated the solution? - I'm not sure of any way to work with nested JSON arrays in the data flow, other than to convert them to a flattened "record" structure.

 

Here is an outline of what I have done to produce this nightmare. 

 

Step 1: The Data Flow has been saved, validated, and free of errors

Step 1

 

Step 2: Perform Data Preview on one of the branches

Step 2

 

Step 3: Perform Data Preview on another branch

Step 3

 

Step 4: Perform Data Preview on "main" branch (after Joins from other branches)

Immediately shows Click to see errors shows No errors

Step 4

Step 5: Navigate around until you find a shape where the Click to see errors shows errors

Step 5

 

Step 6: Re-Validate - Now you have more errors

Step 6

 

Step 7: Click on one of the errors to jump to the offender

Step 7

 

Step 8: Clear the error - Simply select the same column from the drop down

8.1 - Before reselecting the value

Step 8.1

 

8.2 - After reselecting the value (Error has been cleared (greyed out)

Step 8.1

 

Step 9: Repeat Step 8 for all remaining errors

Step 9

 

Stop 10: Pull out all remaining hair and set computer on fire

Step 10

 

azure data factory
Mapping Data Flows

1 Reply

  • jonesb321's avatar
    jonesb321
    Copper Contributor
    Aug 18, 2020

    In response to my question (Am I overcomplicating this?), the answer is YES.  Instead of flattening the JSON Arrays into individual records, I have found you can use the map() function.  Not only does this work with arrays, but nested arrays.  

     

    Here is a quick/simple example that shows that you can access the properties of the object in the array and map it to another value.

     

    map(addresses,
    @(
        address1 = trim(#item.addressOne),
        address2 = trim(#item.addressTwo),
        city = trim(#item.city),
        state = upper(#item.state),
        zip = #item.zip
    )
)

     

    Credit to the Microsoft team for their assistance on this issue.  I am still working with them to identify the root cause of the strange UX behavior.