Azure Synapse write ADLS file with list of file names

Question

I am trying to use Azure Synapse Get Metadata activity to get list of files present in ADLSv2 and the use Lookup activity with following SQL to get just files names

declare @output varchar(max)
set @output ='@{activity('Get Metadata').output.childitems}'
select concat('[',string_agg(name,','),']')  as name from OpenJson(@output) with(name varchar(max) '$.name')

After this I want to store the list into a file using Copy Activity. I have following in Copy activity

When I run this I get following error

"ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'value' property to 'System.String' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.InvalidCastException,Message=Object must implement IConvertible.,Source=mscorlib,'"

What I am missing here ?

I want to have test file filename_list.txt created with list of files present in ADLS directory

details_01_01_2021.json
details_02_01_2021.json
details_03_01_2021.json
details_04_01_2021.json

Answer 1

Error:

"ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'value' property to 'System.String' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.InvalidCastException,Message=Object must implement IConvertible.,Source=mscorlib,'"

The error is occurring because @activity('lookup').output.value gives the value in array of json format. And it is not able to convert into String.

In additional column you can you the below expression.

@replace(replace(replace(replace(activity('Lookup1').output.value[0].name,',','
    '),'"',''),']',''),'[','')

• activity('Lookup1').output.value[0].name: This gets the value of the 'name' property from the first element (index 0) of the output array of a Lookup activity named 'Lookup1'. This assumes that the Lookup activity is configured to return an array of objects and that each object has a 'name' property.

• replace(..., ',', '\n'): Replaces commas (,) with newline characters (\n). This is to format the string by replacing commas with line breaks.

• replace(..., '"', ''): Replaces double quotation marks (") with an empty string. This is used to remove double quotes from the string.

• replace(..., ']', ''): Replaces closing square brackets (]) with an empty string. This is used to remove closing square brackets from the string.

• replace(..., '[', ''): Replaces opening square brackets ([) with an empty string. This is used to remove opening square brackets from the string.

Result: