ADF Copy Activity - Emply col issue

Question

Hi Team,

I have a pipeline where I am copying the data from the file to a database. The file had one extra empty column in between, and my copy activity failed with the below error:

Message: Invalid Excel header with empty value

It failed because the structure got changed and it didn't get mapped for that empty column.

Is there any way to handle this dynamically so that I won't get this error in the future in copy activity?

Answer 1

You can use a Derived Column transformation :

iif(columnName == null || trim(columnName) == '', null, columnName)

This will convert any empty or null column values to a consistent null value.

Then you can add a Filter transformation to remove empty columns :

length(trim(columnName)) > 0

If your file schema changes frequently, you can further enhance your solution to handle dynamic schemas using parameters and metadata-driven approaches in ADF :

1. Parameterize Column Names:
   • Use parameters in your Data Flow to handle column names dynamically.
   • Pass the column names as parameters to the Derived Column and Filter transformations.
2. Use Metadata Activity:
   • Use the Metadata activity to get the schema of the source file.
   • Pass this schema information as parameters to the Data Flow.