question

ewinkiser avatar image
0 Votes"
ewinkiser asked MartinJaffer-MSFT answered

URGENT - In Final Testing - Mapped Array when NULL is causing Error

Hi ALL! @nasreen-akter @MartinJaffer-MSFT @KranthiPakala-MSFT

I have an Array in our json source for a Copy Activity:
"phoneNumbers": [
{
"isPrimary": true,
"type": "Personal Cellular",
"phoneNumber": "440/364-6078"
}
],

I have this mapped as an Array with Collection Reference Checked, and all works great when there is data in the json file for phoneNumbers which is Source.

HOWEVER, today in Final Testing someone created a json file in which this array is like this:
"phoneNumbers":null,

This caused a failure error as follows below. How can I handle the mapping with a null is present in the data file for phoneNumbers like this and overcome this error? The pipeline works GREAT as long as there is "phoneNumbers" data.

I must be able to solve this so that we can Go Live with this pipeline.
Thanks!!
Mike Kiser



Error:
Operation on target Copy SSFEmployeesBlob failed: Failure happened on 'Source' side. ErrorCode=UserErrorTypeInSchemaTableNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to get the type from schema table. This could be caused by missing Sql Server System CLR Types.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidCastException,Message=Unable to cast object of type 'System.DBNull' to type 'System.Type'.,Source=Microsoft.DataTransfer.ClientLibrary,'




azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MartinJaffer-MSFT avatar image
1 Vote"
MartinJaffer-MSFT answered

Hello again @ewinkiser . I have bad news for you this time.

null is different than an empty array [] . I don't think we can make it work. Technically I think "null" is the wrong value to use in the case of there being no phone numbers. While null might make sense from a buisness perspective, it breaks the assertion that phoneNumbers is a complex data type. You make this assertion by choosing phoneNumbers as a collectionReference.

There are broadly, two kinds of data objects, Primitive and Complex. Primitive data types are ones which have simple values, such as numbers, true/false, and characters. Complex data types are containers for multiple primitives , or other complexes. Null falls under the category of primitive.

Data Flow could handle it, I think. This is because I could write some custom expressions in Dataflow to do one thing if the value is null, and a different thing if the value is anything else.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MartinJaffer-MSFT

Hello again and thanks for the explanation; I understand what you are saying. I'll have to decide how to tell the Business so that they understand.

Do you think this would work:

"phoneNumbers": [
{
"isPrimary": true,
"type": "Personal Cellular",
"phoneNumber": "null"
}
],

You always have amazing knowledge and explanations and I appreciate them!
Thanks,

Mike Kiser

0 Votes 0 ·

Yes that is exactly what I would recommend. The way you still provided the fields, but moved the nulls to the members. That should work perfectly.

I will now move this thread from comment to answer so you can mark as accepted answer, @ewinkiser .

0 Votes 0 ·