topic API data changed and it's not compatible with my warehouse table in Pipelines

API data changed and it's not compatible with my warehouse table

CrhIT — Mon, 01 Jul 2024 15:42:49 GMT

I made a pipeline that GET data from an API. At first the table was built with the data extracted. Now the data retrieve from the API changed its structure and thus the pipeline is retrieving errors.

One of them is that a column doesn't find a match the warehouse table.

I was wondering if there's a way to catch up this error, or allow the data table to add a new column, so if further changes are introduce on the API it diesn't mess up with my pipeline.

ERORRS:

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'orders.items.modifiers.item_id' not found in SQL DW table '[dbo].[SaintRemi]',Source=Microsoft.DataTransfer.ClientLibrary,'

One or more 'sink' in copy activity mapping doesn't point to any data. Choose one of the three properties 'name', 'path' and 'ordinal' to reference columns/fields.

Re: API data changed and it's not compatible with my warehouse table

Anonymous — Tue, 02 Jul 2024 01:29:51 GMT

Hi CrhIT ,

The error message indicates that the column ‘orders.items.modifiers.item_id’ is not found in your SQL Data Warehouse table ‘[dbo].[SaintRemi]’. This can happen if there’s a mismatch between the source and sink schema or if the column name is incorrect.

I think spelling and case issues also need to be checked when ensuring that 'orders.items.modifiers.item_id' exists.Another thing is that in the copy activity, go to the Mapping tab and ensure that the source and sink columns are correctly mapped. You can use the properties ‘name’, ‘path’, or ‘ordinal’ to reference the columns/fields. You can look at this document: Schema and data type mapping in copy activity - Azure Data Factory & Azure Synapse | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: API data changed and it's not compatible with my warehouse table

CrhIT — Tue, 02 Jul 2024 07:41:48 GMT

Thank you very much for your reply, the thing is that the API add a new child in its json and the data warehouse didn't have that column when I first built it, hence I cannot mapped it now. I was wondering if there's a work arroound to ignore this "new column" cause I don't truly need it or to create a new column in an existing table in warehouse

Re: API data changed and it's not compatible with my warehouse table

CrhIT — Wed, 03 Jul 2024 18:11:50 GMT

No answers to this topic. I might create a ticket to support.

Re: API data changed and it's not compatible with my warehouse table

Anonymous — Wed, 24 Jul 2024 08:53:21 GMT

Hi CrhIT ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou