CODEWITHSUNDEEP
azure-blob-storageazure-data-factory
Anirudh Mounasamy
Anirudh Mounasamy

Reputation: 1

Transfer CSV files from azure blob storage to azure SQL database using azure data factory

I need to transfer around 20 CSV files inside a folder named ActivityPointer in an azure blob storage container to Azure SQL database in a single data factory pipeline, but ActivityPointer contains 20 CSV files and another folder named snapshots inside it. So when I try to create a pipeline and give * to select all the CSV files inside ActivityPointer it includes the snapshots folder too, which should not be included. Is there any possibilities to complete this task. Also I can't create another folder to transform the snapshots folder into it. What can I do now? Anyone can please help me out.

Upvotes: 0

Views: 321

Answers (2)

Saideep Arikontham
Saideep Arikontham

Reputation: 6124

Copy data is also considering the inner folder while using wildcards (even if we use .csv in wildcard file path). So, we have to validate whether it is a file or folder. Please look at the following demonstration.

  • First use Get Metadata on the required folder with field list as Child items. The debug output will be:

enter image description here

  • Now use this to iterate through child items using For each activity.
@activity('Get Metadata1').output.childItems

enter image description here

  • Inside for each, use if condition activity to check whether the current item is a file or not. Use the following condition.
@equals(item().type,'File')

enter image description here

  • When this is true, you can use copy data to complete copying the file to target table (Ignore the false case). I have create file_name parameter in my source dataset passing its value as @item().name().

enter image description here

  • This will help you to achieve your requirement. The following is the debug output. I have 4 files and 1 folder. The folder will be ignored, and the rest will be copied into the target table.

enter image description here

Upvotes: 0

Nandan
Nandan

Reputation: 4945

Assuming you want to copy all CSV files within ACtivityPointer folder, You can use wildcard expression as below :

enter image description here

you can provide path till Active folder and than *.csv

Upvotes: 0

Related Questions