Share via

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

Tony Johansson 40 Reputation points
2023-04-17T10:10:45.74+00:00

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 User's image

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Suba Balaji 11,206 Reputation points
    2023-04-17T11:51:21.64+00:00

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

    $$filepath

    Screenshot_2023-04-17-17-02-43-84_40deb401b9ffe8e1df2f1cc5ba480b12

  2. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2023-04-17T21:38:36.8266667+00:00

    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.

    User's image

    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.

    User's image

    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