Data Factory Pipeline JSON to SQL Table
$30-250 AUD
Pagado a la entrega
Evening,
I would like to use the Azure Data Factory to move data in my blob (File One Link: [url removed, login to view]!At8Q-ZbRnAj8hjRk1tWOIRezexuZ File Two Link: [url removed, login to view]!At8Q-ZbRnAj8hjUszxSY0eXTII_o ) which is currently in blob format but is json inside to an sql table.
I have tried to do it at the moment but I am having an issue with nesting out all of the tables using the “copy data” data factory function as to extract all of the possible data as well as then being able to copy it to the azure sql table.
When I attempt to run the pipeline I receive this error ([url removed, login to view] ).
I have an approximate feeling as for why it is happening but I am not sure how to solve the problem. I feel it has everything to do with the structure of sql table.
Diagram of my data factory: [url removed, login to view]
Error Code: [url removed, login to view]
Log Output: [url removed, login to view]
Error Panel: [url removed, login to view]
ErrorLog in json: [url removed, login to view]!At8Q-ZbRnAj8hjnqUvVvjhajhTfk
InputDatasetPanel: [url removed, login to view]
Input Dataset Table schema: [url removed, login to view]
OutputDatasetPanel: [url removed, login to view]
Output Dataset Table Schema: [url removed, login to view]
InputDataSet Data Factory Code: [url removed, login to view]!At8Q-ZbRnAj8hjadejMWk4GV75lE
OutputDataSet Data Factory Code: [url removed, login to view]!At8Q-ZbRnAj8hjhoOraB2yANDIAf
Pipeline Source Code: [url removed, login to view]!At8Q-ZbRnAj8hjcVLTypeZwWw-rK
Evening,
Update:
I have worked out that it is an issue with the nested arrays in the json file (https://1drv.ms/u/s!At8Q-ZbRnAj8hjWvXpDZ4qUVNu6G).
When I delete the non nested arrays from the json file it copies perfectly to my sql table. After including one nested array in my json file I receive the sql error 4815 (https://gyazo.com/b380edabfb4eae277fcb43b18c27d9bd ). I have increased the varchar length of the newly added columns and I’m still receiving the error. The error has everything to do with how the nested columns are being expanded/handled/mapped.
This before I expand the array (https://gyazo.com/88fe22014f7858bfcccf6ba40cab3dd7 & https://gyazo.com/cbe4f94b264620f16cf38f663836892a ).
Once I expand the array using the data factory “expand array” link next to the “inclusive_tax” column (https://gyazo.com/a90f49d9385966d9e83f40eadc463c0f & https://gyazo.com/6611ad6cddc65fca840beeda5903d21a ). From “name” is the expanded nested array aspect. I feel the column to the right is the issue as the json path isn’t correct and does not properly represent that it was expanded from a nested array.
Here is the schema of the json file (https://gyazo.com/7272bcf76e73bf791b4ae6714bf04084 & https://gyazo.com/5b380895196a6ede0f2ed18de5847d18 & https://gyazo.com/00cea844b25a4946d6548c7aca52f7d4 ).
Here is the schema mapping of the json file to my sql table (https://gyazo.com/4c1437231a030bbfa91863633412e4a6) All of the type’s are appropriate and I think accommodative.
When I run the data factory copy activity I receive the error (https://gyazo.com/b380edabfb4eae277fcb43b18c27d9bd) and I’m not sure how to solve it. I believe it has everything to do with how the json path of the expanded nested arrays.
Might be a helpful link: https://github.com/Microsoft/azure-docs/blob/master/includes/data-factory-file-format.md#specifying-jsonformat
Nº del proyecto: #13184294
