Search code examples
jsonazureazure-data-factory

Load raw JSON into a single row in Azure SQL using Data Factory

So the scenario is that I have a .json file stored in a Data Lake, which I've fetched from an API.

I now want to copy the raw JSON into an Azure SQL table as a single string into a single column/row, after which I'll use a stored procedure to actually parse the data.

I can't figure out how to just copy the JSON string though. I've been trying to use a Copy Data activity but it wants me to specify mappings for all the nested elements. I tried ticking "Map complex values to string" and mapping a collection reference to my target column but that doesn't work either, the data comes back as NULL.

Solution

  • I managed to do this by using a .csv dataset rather than a json dataset. Then I could load it as a single string into the database.