ADF Copy Activity - Emply col issue

ADF_Coder 0

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?

User's image

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-30T16:24:51.3133333+00:00

Hi @ADF_Coder

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

2 answers

Amira Bedhiafi 26,261 Reputation points

2024-07-20T10:24:05.6666667+00:00
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 :

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.

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.
Please sign in to rate this answer.

1 person found this answer helpful.
ADF_Coder 0 Reputation points

2024-07-20T14:55:08.76+00:00

Hi @Amira Bedhiafi ,

Thank you for your message.

This we can use in Data flow but I need to do in Copy activity itself.

Please suggest.

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-22T12:21:30.7733333+00:00

Hi @ADF_Coder

Thanks for the question and using MS Q&A platform.

Please refer to the related discussion thread which might help you in getting some useful insights: https://learn.microsoft.com/en-us/answers/questions/751609/how-to-transform-the-data-if-my-excel-file-doesnt

I hope this information helps. Please do let us know if you have any further queries.

ADF_Coder 0 Reputation points

2024-07-23T04:54:18.3366667+00:00

Hi @Chandra Boorla

Thank you for your reply.

I want to copy the columns with the header in SQL. Please suggest and help.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-24T10:34:01.6166667+00:00

Hi @ADF_Coder

Please find the below gif that I have followed to achieve your requirement.

Hope this helps! Let me know if you have any further questions.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
ADF_Coder 0 Reputation points

2024-07-24T16:02:39.2466667+00:00

Hi @Chandra Boorla,

Thank you but this is not what I am looking for. Also the GIF speed is too fast to see whole code.

ADF_Coder 0 Reputation points

2024-07-24T16:05:02.37+00:00

Hi @Chandra Boorla

Thank you for your comment but this is not what I am looking for.Also gif speed is too fast to see whole code.

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-24T18:04:01.56+00:00

Hi @ADF_Coder

Apologies for the inconvenience, here is the updated gif.

For creating a Stored Procedure I have used the below code:

CREATE PROCEDURE Emptycolumnsp2 @SNo INT, @First_Name NVARCHAR(225), @Middle_Name NVARCHAR(225) = NULL, -- Optional parameter @Last_Name NVARCHAR(225), @Email_ID NVARCHAR(225), @City NVARCHAR(225) AS BEGIN INSERT INTO [dbo].[Reprotable] (SNo, First_Name, Middle_Name, Last_Name, Email_ID, City) VALUES (@SNo, @First_Name, @Middle_Name, @Last_Name, @Email_ID, @City); END;

Could you please share some additional information of your query with an example? This will help me assist you better.

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-26T15:54:00.0666667+00:00

Hi @ADF_Coder

If the provided information does not accurately capture the issue you are experiencing, could you please provide additional context or clarify your query with a specific example? This will enable me to better understand your requirements and provide a more accurate and effective solution.

ADF_Coder 0 Reputation points

2024-07-27T04:29:18.65+00:00

Hi @Chandra Boorla ,

Checking your steps.

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-07-29T17:16:40.8233333+00:00

Hi @ADF_Coder

Just checking in to see if you have had a chance to look over.

Please let me know if you have any further questions.

ADF_Coder 0 Reputation points

2024-07-30T16:50:52.67+00:00

Hi @Chandra Boorla ,

I don't want that empty column at all in the database table. I want to skip that empty column from the file and load the rest of the of the columns in the database table.

Please suggest.

Chandra Boorla 2,605 Reputation points Microsoft Vendor

2024-08-02T12:29:54.78+00:00

Hi @ADF_Coder

You can use Mapping column to skip the empty column from the file and load the rest of the columns in the database table. I hope this information helps.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ADF Copy Activity - Emply col issue

2 answers

Your answer