I need to use feature additional column to in source when insert into sql server but no column is created

Question

Hello! I have a ForEachFile activity that get the files from GetMetaData.The files is json. Within this ForEachFile activity I have a copy that should insert the contents into a sql server database table and in addition add the current filename as an extra column to the database table. So I have google and find several example where they use the feature additional columns. So I thought that this should be easy. So in the additional columns in the Source I add a dynamic column with Filename and value as @item.name because each iteration in the ForEachFile loop will have @item.name as the filename. Here is a screen shot for the Source

All the values in the json file is inserted into the database but the Filename is empty. If I create the table first and then run the pipeline I can see that the Filename column is null. If I let sink create the table I can see that no Filename column is created. Many thanks in advance
Tony Johansson

Answer 1

Hi @Tony Johansson You can use the below expression in the additional column, as shown below and let us know if that works.

$$filepath

Answer 2

Hi @Tony Johansson ,

Thanks for using MS Q&A forum and posting your query here.

Sorry for your experience with this issue. I just tried to reproduce the problem but no luck.

I tried with additional column fileName = $$FILEPATH as well as fileName = @item().name and in both cases, my file name was written to Azure SQL table.

Here is the output, I tried multiple combinations and each time the file name has been written to the sink table using additional column feature.

The strange part with your scenario was that file name was passing correctly since you have confirmed that the file data is being copied as expected and the issue is only with the additional column feature. But when I tried the same, I see that additional column value (file name) was copied successfully.

If we suspect that the @item().name is something creating issue, then the file data also shouldn't have copied to the table, and if that data is copied, the additional column value should be added to the table too.

The behavior somehow seems specific to your specific instance. Hence would recommend filing a support ticket for deeper investigation. In case if you don't have a support plan, please let me know so that I can work with you offline in creating a onetime free support ticket.

Looking forward to your response.

Thank you