ADF Copy data from Table to Azure Blob with headers

Question

I am using ADF to copy data from Snowflake table (Source) to Azure Blob CSV file (Sink).

The Sink dataset has “First row as header” checked.

It works well if the source table contains data.

However, if the source table is empty, the generated file is empty without a header.

Please see the screenshots for the settings.

I wonder how to make the generated file with header regardless of whether the source table is empty or not.

Thank you.

Answer 1

You can try the below workaround, to copy the header into the target file.

• First Check the table count using lookup activity. Then use if activity to check whether the count is 0 or not.
• Add two copy activities one in True activities of if and another is in false activities.
• You need to use different target datasets with same target file path for the two copy activities.
• One copy activity is to copy the headers and if the count is not 0, other one is for regular copy to target file.

Use the below query in the lookup to get the count of the table.

select count(*) as count from emp

Then give the below expression in the if activity.

@equals(activity('Lookup1').output.value[0].COUNT,0)

Inside True activities of if, use the below query for the source of the copy activity.

select listagg(column_name,',') from information_schema.columns
where table_schema ilike 'public' and table_name ilike 'emp'

If the column delimiter in your target file is |, you need to use | instead of , like this listagg(column_name,'|') in the above query.

This will give the Delimiter(,) seperated column names as one row with a column name LISTAGG(COLUMN_NAME,',') like below.

LISTAGG(COLUMN_NAME,',')
Id,name

But here, we only want the row which has required headers not the column name.

So, in the sink dataset of this copy activity, uncheck the First row as header and give the below configurations. This is the reason to use different datasets for both copy activities.

You can change the Column delimiter as per your requirement, but you need to change that in the query as well.

In the False activities of if, Give your regular copy activity with another target dataset.

Execute the pipeline and your headers will be copied like below in the Blob.