ADF copy data to SQL from array variable

Question

ADF copy data to SQL from array variable

Praveen 1

In ADF pipeline am constructing JSON Array variable with multiple records. Is there any way to copy activity to Azure SQL from JSON Array variable? How do I map columns in Copy Activity mapping from this array?

Thanks,
Raj

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-08-03T11:04:55.137+00:00

Hi @PraveenAvadutha-9591 ,

Following up to check is below provided answer by @Nandan Hegde helps you? If yes, Please Accept Answer. Accepting answer may help community too.

Please let us know if any further queries. Thank you.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-08-06T05:30:23.587+00:00

Hi @PraveenAvadutha-9591 ,

Just checking is below provided answer by @Nandan Hegde helps you? If yes, Please Accept Answer. Accepting answer may help community too.

Please let us know if any further queries. Thank you.
Lodewijk Wiggers 6 Reputation points

2021-12-12T17:02:48.57+00:00
If you want to write the content of a variable of type Array, there is a workaround which works fine. Goal: write content of your array as 1 line per value of the array into a file

variable : [ a, b, c]
to
file content:
a
b
c

Steps:

Create an 'empty' file with 1 row , can be a json file or something else with just 1 row

Use the Copy Data activity with the single row file as source and your sink your target file

Use the additional column mechanism

Expand-join-with-carriagereturn the array variable with use of @fr and @decodeUriComponent -> @fr (variable,decodeUriComponent('%0A'))

Yes, it is a flaw that Microsoft doesn't have an @Mac (int) function to create a special character.(or I am an idiot and doesn't know the right way to concat an '\n' , which I tried but didn't work.)

1 answer

Your answer

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-08-03T11:04:55.137+00:00

Hi @PraveenAvadutha-9591 ,

Following up to check is below provided answer by @Nandan Hegde helps you? If yes, Please Accept Answer. Accepting answer may help community too.

Please let us know if any further queries. Thank you.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-08-06T05:30:23.587+00:00

Hi @PraveenAvadutha-9591 ,

Just checking is below provided answer by @Nandan Hegde helps you? If yes, Please Accept Answer. Accepting answer may help community too.

Please let us know if any further queries. Thank you.
Lodewijk Wiggers 6 Reputation points

2021-12-12T17:02:48.57+00:00

If you want to write the content of a variable of type Array, there is a workaround which works fine. Goal: write content of your array as 1 line per value of the array into a file

variable : [ a, b, c]
to
file content:
a
b
c

Steps:

Create an 'empty' file with 1 row , can be a json file or something else with just 1 row

Use the Copy Data activity with the single row file as source and your sink your target file

Use the additional column mechanism

Expand-join-with-carriagereturn the array variable with use of @fr and @decodeUriComponent -> @fr (variable,decodeUriComponent('%0A'))

Yes, it is a flaw that Microsoft doesn't have an @Mac (int) function to create a special character.(or I am an idiot and doesn't know the right way to concat an '\n' , which I tried but didn't work.)

Answer 1

Nandan Hegde 36,886 MVP Volunteer Moderator

Hey,
You can refer the below thread which is somewhat similar :
https://learn.microsoft.com/en-us/answers/questions/493585/write-pipeline-variable-value-to-csv-or-text-file.html

0 comments

Share via

ADF copy data to SQL from array variable

1 answer

Your answer