How to update log table with source file name of data flow?

Question

I have nested JSON file and wants to move data into tabular format. Pipeline as below:

  Source --> Flatten --> Flatten --> Filter --> Derived column --> Sink

Dataset created for source to represents ADLS location of input JSON file.

Path of input file is

• Landing / 2024 / 03 / 15 input1_20240315.json input2_20240315.json input3_20240315.json

Final data flow pipeline as:

 Lookup --> Data Flow --> Stored Procedure (Success / Failure)

• Lookup captures start of the pipeline
• Stored Procedure (Success) capture, Pipeline status Rows Read Rows written End of pipeline execution
• Stored Procedure (Failure) capture, Pipeline status Error message

Query: How to update log table with source file name (input1_20240315.json)?

Answer 1

Alright so, along with the existing columns in file, you want to capture the source file name in a column in sink. Correct me if my understanding is not correct.

You can make use of the source options tab and in there, you have a field called column to store file name. You can give a column name there, for instance, Source_Filename. Please check the below image where arrow is pointing

One thing to note here is, once you mention a column name here, your file name along with the folder (except root) structure will be automatically captured in the column name you mention.

If you don't want the column hierarchy, we can add a derived column transformation and take substring of file name alone.

Hope it helps. Please let us know for questions.

Thanks.