CODEWITHSUNDEEP
azure-data-factory
Djbril
Djbril

Reputation: 895

Filename as Column using Data Factory V2

I have a lot of JSON files in Blob Storage and what I would like to do is to load the JSON files via Data factoryV2 into SQL Data Warehouse. I would like the filename in a column for each JSON file. I know how to do this in SSIS but I am not sure how to replicate this in Data Factory.

e.g File Name: CornerShop.csv as CornerShop in the filename column in SQL Data Warehouse

Upvotes: 2

Views: 3872

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Firstly,please see the limitation in the copy activity column mapping:

  • Source data store query result does not have a column name that is specified in the input dataset "structure" section.
  • Sink data store (if with pre-defined schema) does not have a column name that is specified in the output dataset "structure" section.
  • Either fewer columns or more columns in the "structure" of sink dataset than specified in the mapping.
  • Duplicate mapping.

So,i don't think you could do the data transfer plus file name at one time.My idea is:

1.First use a GetMetadata activity. It should get the filepaths of each file you want to copy. Use the "Child Items" in the Field list.

2.On success of GetMetaData activity, do ForEach activity. For the ForEach activity's Items, pass the list of filepaths.

3.Inside the ForEach activity's Activities, place the Copy activity. Reference the iterated item by @item() or @item().name on the blob storage source file name.

4.Meanwhile,configure the filename as a parameter into stored procedure. In the stored procedure, merge the filename into fileName column.

Upvotes: 3

Related Questions