How to store stringified array in Synapse?

Sam Burns 6 Reputation points
2023-06-05T16:41:53.2433333+00:00

We're using Azure Data Factory to grab a CSV file in Azure Storage and dump the CSV data into a table in an Azure Synapse DB. The data dump is currently failing due to multiple columns that are stringified arrays.

The source data in the CSV file looks like this:

'[123, 456, 789]'

We've tried setting the columns in the destination table to be varhcar, nvarchar, nvarchar(max), but nothing is working. Any advice here?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,363 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,528 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2023-06-07T11:23:03.2033333+00:00

    Hi Sam Burns ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your query, you are trying to copy data from csv to sql pool using synapse pipeline and the data is in the form of array enclosed within quotes. Please let me know if that is not correct understanding.

    I manually tried to create table in dedicated sql pool and stored value as you mentioned and it has successfully storing the same: User's image

    Query used: insert into tab1 select '''[123, 456, 789]'''

    So it is not the issue with the datatype. Could you please share the settings you have configured in the copy activity by providing the relevant screenshots so that I can replicate the same to check from my end.

    Looking forward to your response. Thankyou