How to store stringified array in Synapse?

Question

How to store stringified array in Synapse?

Sam Burns 6

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?

1 answer

Your answer

Answer 1

AnnuKumari-MSFT 34,566 Microsoft Employee Moderator

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

Sam Burns 6 Reputation points

2023-06-07T16:30:06.0866667+00:00

Thanks for getting back to me @AnnuKumari-MSFT !

I have a CSV where the source data looks like this (my original post showed single quotes but, in fact, the file has double quotes):

"[123, 456, 789]"

And the destination table in Synapse is of type nvarchar(4000). The job I'm running in Data Factory is a simple Copy data job with no transformations in between. This shows an example of the data type mapping between some of the source and sink columns:

Do you see any issues here?
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-06-19T09:10:13.0566667+00:00

Hi Sam Burns , I don't see any issue here. In CSV files default datatype will be string only and same you are coping in to SQL table as string only. Then it should work. Kindly retry and see. If still it not helps, could you please share your error details which u are getting?
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2023-06-20T08:36:36.1666667+00:00

Hi Sam Burns ,Just checking if you get chance to share updates on above? Thank you.

Share via

How to store stringified array in Synapse?

1 answer

Your answer