ADF Copy Activity with Additional Columns is Not working

Question

My goal is to add some text to a file. I used this post here to help me design this setup.

I have a set variable that sets a variable with some value. I then have a copy activity. This copy activity has an empty file for the source. I then add in an additional column that uses my set variable. In sink I have a different file I hope to copy the contents of set variable to. When I run in debug mode, the sink file ends up empty. The additional column is not being copied over. I tried with a mapping and then without. In both cases the same thing happened.

Does anyone have any ideas or tips?

Answer 1

I have tried and also seen the same empty data in the sink file as you mentioned.

Sink file:

You can raise the support ticket or feel free to post your question in Microsoft Q&A forum where the product team will monitor them closely.

Answer 2

I'm not sure the file based copy activity is supposed to work this way, you need input data/rows for it to enumerate. If you have access to SQL server then you could simulate a dynamic SQL query to include the contents of your variable as a tabular output. This can then define a source set of rows that could be appended into a file.

For example, SQL for Source Query:

/*
    Bring in the value from your variable. use @@ to escape the SQL parameter name
    NOTE: Do not forget to escape single quotes if they exist in your string, replace ' with ''
*/
declare @@my_sql_var nvarchar(max) = '@{variables('fileContents')}';

/*
    Do some work here with this variable 
*/

/*
    Return the tabular response for the copy activity source query.
*/

select @@my_sql_var [VariableName];

Copy Activity - SQL Source

Dynamic Expression for Source Query

Preview Output