Question 287186 in Azure Question posted in
The official documentation to get you started can be found here.

ADF Copy data from Table to Azure Blob with headers

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.

source
sink
blob

Tags:

2

Answers


  1. 0

    In Copy activity under source tab you are using query as SELECT * FROM. This query will not return any schema also. Try using different query which returns schema or table structure with no rows and then see if that helps.

    If still after trying above, if it does not work then that means its product limitation.

    Login or Signup to reply.
  2. 0

    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

    enter image description here

    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'

    enter image description here

    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.

    enter image description here

    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.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search