How to avoid failed when input blob is missing in Azure Data Factory copy activity

Question

I have a pipeline with three activities: 1. Download file from external web site and store it in blob storage (custom activity) 2. Copy data from blob to Azure SQL DB (Copy activity) 3. Delete blob file (custom activity)

The files shall be downloaded once/month, but I don't know which day it will be made available, so I have scheduled the pipeline to run once/day. If the file is available, it is downloaded. Activity #1 works fine, but activity #2 fails if there is no input file. Is there any way to avoid the Failed result, since it's an expected outcome?

Answer 1

There is a way to handle this situation in Azure Data Factory Version 1 by adding a policy custom code . But i tried in ADF v2 and doesn't works.

    "policy": {
        "validation": {
            "minimumSizeMB": 0.01
        }
    }

Answer 2

One approach which I tried out successfully is to add a Get Metadata activity before the copy activity.

In the GetMetadata activity, you can add the same dataset, in the field list, you can add 'Exists'. You can consume the response of 'getmetadata' in a IF-Else activity, with the condition '@bool(activity('GetMetadataActivityName').output.exists)' If the blob exists, the true part of If-else activity, you can add the Copy activity there. You can ignore the else part of If-else.

Sample pipeline

Answer 3

I ran into this exact same issue. From what I understand the only way to avoid the failure would be to create a blank blob file. The copy activity will no longer fail, it will just not copy any data because it is blank. I have confirmed this with an azure data factory PM as I said I ran into the same issue.

Hope this helps.