Data Factory - exception handling with files with no data rows but headers only

Today we will learn on how to apply exception handling when loading csv files into Azure SQL database that has no data rows but one header line

Scenario: Suppose you want to load data into Azure SQL incrementally that from csv files generated every N minutes into Azure data lake storage (ADLS). You want to apply exception handling to make sure when empty files comes in, data factory will not fail.

Solution: Please make sure you are generating empty files (from the source) when there is no data (i.e.: without header row). Data factory will succeed if the file is completely blank. This is out of the box data factory's functionality. In case of header row, it will fail. So tweak your source logic.

Steps after source file is generated:

• Create a Get Metadata task in data factory
• In the Field list, specify an argument "exists"
• We will use this argument in the if condition to see if the file exists in the folder. 
• This is done to prevent from error "path doesn't exist / invalid source"

• Drag "If Condition" task and connect it to the Get Metadata task (as shown above in the first picture)

• Specify an expression to check if the file exists (dataset path is valid)
• Expression: @and(bool(activity('check if file exists').output.exists),bool(true))

• In the True condition, insert your incremental load logic.
• copy activity to copy data from source to target

• You are done.
• Run your Pipeline.
• Here after successfully loading data into SQL, i am moving the file to an archive ADLS container.

Note: Above image shows that now both blank files (0 bytes) as well as files with data rows are being copied to the Archive folder after successful load in SQL.

• In case of Blank file without header row, Data factory will succeed and proceed to the next task. No data is written in the database and data factory pipeline is successful.

Azure Data Factory - Upsert using Pipeline approach instead of data flows

Today we will learn on how to perform upsert in Azure data factory (ADF) using pipeline approach instead of using data flows

Task:

• We will be loading data from a csv (stored in ADLS V2) into Azure SQL with upsert using Azure data factory. Once the data load is finished, we will move the file to Archive directory and add a timestamp to file that will denote when this file was being loaded into database

Benefits of using Pipeline:

• As you know, triggering a data flow will add cluster start time (~5 mins) to your job execution time. Although, you can make use of the Time to live (TTL) setting in your Azure integration runtime (IR) to decrease the cluster time but, still a cluster might take around (2 mins) to start a spark context. so, if you are thinking of creating a real time data load process, the pipeline approach will work best as it does not need a cluster to run and can execute in seconds.

Pre-requisites

• An Azure Data Factory resource
• An Azure Storage account (Gen 2)
• An Azure SQL Database