Search code examples
azure-synapseazure-synapse-pipeline

Preserving the data type in a different file format

I'm currently looking to import some data from SQL Server, on the SQL Server it has its data types correctly set in the schema. Unfortunately, I am unable to use parquet or ORC files for what I am doing so I want to preserve the data type in a different file format. I have recently tried JSON, which seems to work when I click import schema on the mapping, but I want to apply the logic to a ingest pipeline loop that pulls in around 20 different tables, but we have no idea how to do it.

enter image description here

The attached print screen is the copy activity within the for each loop, if it was just a singular copy activity, I would just open up mapping and click import schema, but I'm not sure how to do this within the for each loop

I have gone through the Microsoft documentation, but could not see anything of use.

Solution

  • You can use JSON datasets for this scenario. Copy activity will not change the data types when the schema is empty in the mapping. It will give the same mapping as source in the target JSON files.

    Inside the ForEach, use JSON dataset as sink. Give the filename for each iteration using dataset parameter and select Array of objects as File pattern.

    enter image description here

    Leave the mapping as it is. Don't import any mapping.

    enter image description here

    It will preserve the data types for every JSON file.

    enter image description here