Azure Data Factory: Cannot save the output of Set Variable into file/Database

Question

I'm trying to store a list of file names within an Azure Blob container into a SQL db. The pipeline runs successfully, but after running the pipeline, it cannot output the values (file names) into the sink database, and the sink table doesn't get updated even after the pipeline completed. Followings are the steps I went through to implement the pipeline. I wonder which steps I made mistake.

I have followed the solutions given in the following links as well:

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview#add-additional-columns-during-copy

Transfer the output of 'Set Variable' activity into a json file [Azure Data Factory]

Steps: 1- Validating File Exists, Get Files metadata and child items, Iterate the files through a foreach.

2- Variable defined at the pipeline level to hold the filenames Variable Name: Files, Type: string

3- parameter defined to dynamically specify the dataset directory name. Parameter name: dimName, parameter type: string

4- Get Metadata configurations

5- Foreach settings @activity('MetaGetFileNames').output.childItems

6 - Foreach Activity overview. A set Variable to set the each filename into the defined variable 'files'. Copy Activity to store the set value into db.

7- set variable configuration

8- Copy Activity source configuration. Excel Dataset refers to an empty excel file in azure blob container.

9- Copy Activity sink configuration

10-Copy Activity: mapping configuration

Answer 1

1. Instead of selecting an empty excel file, refer to a dummy excel file with dummy data.

Source: dummy excel file

2. You can skip using Set variable activity as you can use the Foreach current item directly in the Additional column dynamic expression.

3. Add additional columns in the Mapping.

4. Sink results in SQL database.