I'm trying to create a Data Flow in ADF that will read my JSON file and eventually put the data into a DB. However, I keep getting the following error:
at Source '': Malformed records are detected in schema inference. Parse Mode: FAILFAST. It could be because of a wrong selection in document form to parse json file(s). Please try a different 'Document form' (Single document/Document per line/Array of documents) on the json source.
So, I've scoured the internet, including StackOverflow, and have not found an answer to this problem. Everything I've seen states that you need to select the Document form type and I choose Array of Documents. And I still get the error.
Here is my JSON:
[
{
"DriverGroups": [
{
"id": "b27A9"
},
{
"id": "b278A"
}
],
"Keys": [],
"LicenseNumber": "xxx1",
"LicenseProvince": "OH",
"ViewDriversOwnDataOnly": false,
"AcceptedEula": 19,
"ActiveDashboardReports": [],
"ActiveDefaultDashboards": [],
"ActiveFrom": "2023-02-01T16:08:40.618Z",
"ActiveTo": "2050-01-01T00:00:00.000Z",
"AuthorityAddress": "an address 1",
"AuthorityName": "company name 1",
"AvailableDashboardReports": [],
"Bookmarks": [],
"CannedResponseOptions": [],
"CarrierNumber": "thenumber",
"ChangePassword": false,
"Comment": "",
"CompanyAddress": "adress 1",
"CompanyName": "company name",
"CountryCode": "",
"DateFormat": "MM/dd/yy HH:mm:ss",
"CompanyGroups": [
{
"id": "b27A9"
},
{
"id": "b278A"
}
],
"DefaultGoogleMapStyle": "Roadmap",
"DefaultHereMapStyle": "Roadmap",
"DefaultMapEngine": "MapBox",
"DefaultOpenStreetMapStyle": "MapBox",
"DefaultPage": "helpGuide",
"Designation": "",
"DisplayCurrency": "Cad",
"DriverGuideVersion": 0,
"ElectricEnergyEconomyUnit": "MPGEUS",
"EmployeeNo": "empno",
"FeaturePreview": "",
"FirstDayOfWeek": "Sunday",
"FirstName": "firstname",
"FuelEconomyUnit": "MPGUS",
"HosRuleSet": "America8DayBig",
"Id": "b2053",
"IsAdverseDrivingEnabled": true,
"IsDriver": true,
"IsEulaAccepted": true,
"IsEmailReportEnabled": true,
"IsExemptHosEnabled": false,
"IsLabsEnabled": false,
"IsMetric": false,
"IsNewsEnabled": true,
"IsPersonalConveyanceEnabled": false,
"IsServiceUpdatesEnabled": false,
"IsYardMoveEnabled": true,
"JobPriorities": [],
"Language": "en",
"LastName": "lastname",
"MapViews": [
{
"name": "North America",
"viewport": {
"x": -500,
"y": 129,
"width": 145.5,
"height": -52
},
"highlightGroups": []
}
],
"MaxPcDistancePerDay": 0,
"MediaFiles": [],
"Name": "a name",
"Password": null,
"PhoneNumber": "",
"PhoneNumberExtension": "",
"PrivateUserGroups": [],
"ReportGroups": [],
"SecurityGroups": [
{
"id": "12365"
}
],
"ShowClickOnceWarning": true,
"TimeZoneId": "America/New_York",
"WifiEula": 0,
"ZoneDisplayMode": "Default",
"Version": null
},
{
"DriverGroups": [
{
"id": "b27A4"
},
{
"id": "b278A"
}
],
"Keys": [],
"LicenseNumber": "bbbbb3",
"LicenseProvince": "PA",
"ViewDriversOwnDataOnly": false,
"AcceptedEula": 19,
"ActiveDashboardReports": [],
"ActiveDefaultDashboards": [],
"ActiveFrom": "2022-01-13T15:42:41.318Z",
"ActiveTo": "2022-01-16T15:54:00.000Z",
"AuthorityAddress": "auth address",
"AuthorityName": "auth comp name",
"AvailableDashboardReports": [],
"Bookmarks": [],
"CannedResponseOptions": [],
"CarrierNumber": "xxxxx",
"ChangePassword": false,
"Comment": "",
"CompanyAddress": "company address",
"CompanyName": "Company1",
"CountryCode": "",
"DateFormat": "MM/dd/yy HH:mm:ss",
"CompanyGroups": [
{
"id": "b27A4"
},
{
"id": "b278A"
}
],
"DefaultGoogleMapStyle": "Roadmap",
"DefaultHereMapStyle": "Roadmap",
"DefaultMapEngine": "MapBox",
"DefaultOpenStreetMapStyle": "MapBox",
"DefaultPage": "helpGuide",
"Designation": "",
"DisplayCurrency": "Cad",
"DriverGuideVersion": 0,
"ElectricEnergyEconomyUnit": "MPGEUS",
"EmployeeNo": "emplno",
"FeaturePreview": "",
"FirstDayOfWeek": "Sunday",
"FirstName": "firnst",
"FuelEconomyUnit": "MPGUS",
"HosRuleSet": "America8DayBig",
"Id": "b81F",
"IsAdverseDrivingEnabled": true,
"IsDriver": true,
"IsEulaAccepted": true,
"IsEmailReportEnabled": true,
"IsExemptHosEnabled": false,
"IsLabsEnabled": false,
"IsMetric": false,
"IsNewsEnabled": true,
"IsPersonalConveyanceEnabled": true,
"IsServiceUpdatesEnabled": false,
"IsYardMoveEnabled": true,
"JobPriorities": [],
"Language": "en",
"LastName": "lastname 2",
"MapViews": [
{
"name": "North America",
"viewport": {
"x": -66,
"y": 125,
"width": 145.5,
"height": -52
},
"highlightGroups": []
}
],
"MaxPcDistancePerDay": 0,
"MediaFiles": [],
"Name": "his/her name",
"Password": null,
"PhoneNumber": "",
"PhoneNumberExtension": "",
"PrivateUserGroups": [],
"ReportGroups": [],
"SecurityGroups": [
{
"id": "b27C9"
}
],
"ShowClickOnceWarning": true,
"TimeZoneId": "America/New_York",
"WifiEula": 0,
"ZoneDisplayMode": "Default",
"Version": null
}]
Has anyone else encountered this even after choosing Array of documents? If so, how did you solve the problem? Any help would be greatly appreciated.
In case anyone else runs into this issue. I've found a workaround to this problem. I took the list of objects that I had and wrote them out to a file like this:
{"DriverGroups":[{"Id":"b27A9"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"VD123456","LicenseProvince":"NC","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2020-07-01T16:08:40.618Z","ActiveTo":"2050-01-01T00:00:00.000Z","AuthorityAddress":"555 Some Rd, A City, CA 12345","AuthorityName":"Company 1","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"123456","ChangePassword":false,"Comment":"","CompanyAddress":"555 Some Rd, A City, CA 12345","CompanyName":"Company 1","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b27A9"},{"Id":"b278A"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"US","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP1","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FIRSTNAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DayBig","Id":"A123","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":false,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LASTNAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-121,"Y":59,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
{"DriverGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"24681012","LicenseProvince":"CA","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2021-10-13T15:42:41.318Z","ActiveTo":"2032-01-16T15:54:00.000Z","AuthorityAddress":"333 Any ST, Another City, TX 22222","AuthorityName":"Company 2","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"246810","ChangePassword":false,"Comment":"","CompanyAddress":"333 Any ST, Another City, TX 22222","CompanyName":"Company 2","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"EUR","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP2","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FNAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DayBig","Id":"B246","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":true,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LNAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-200,"Y":49,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME1","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
{"DriverGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"36912150","LicenseProvince":"MI","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2022-11-26T17:46:58.000Z","ActiveTo":"2045-05-19T12:00:00.000Z","AuthorityAddress":"4444 Random Ave, Yet Another City, IA 33333","AuthorityName":"Company 3","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"369121","ChangePassword":false,"Comment":"","CompanyAddress":"4444 Random Ave, Yet Another City, IA 33333","CompanyName":"Company 3","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b278A"},{"Id":"b27A4"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"Cad","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP3","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FIRST_NAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DaySleeper","Id":"C369","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":false,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LAST_NAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-171,"Y":19,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME2","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
I then created a Dataset using the file. I then created a Data flow and pointed to my Dataset and selected for my Document form (JSON settings) >> Document per line. Using this format allowed me to preview my data.
I hope this helps!