pipeline should re-run even if tables are failed due to column mismatch

Ram Babu 50

Hi Team,

I need to handle the scenario where the structure of the source table changes then pipeline should run for failed tables again using drop the table and create new table.

i have design the pipeline using lookup activity, foreach activity and copy activity for 12 tables. source is salesforce and target is azure synapse dedicate pool. every day pipeline runs successful.

Today 11 tables are successful and 1 table failed due to column mismatch in target table in that pipeline.

i have to add some activities (please advice) to re-load if any tables failed. (like drop the table and auto create table.

Please advice how to design pipeline to avoid if source side structure change scenarios.

Thanks

Smaran Thoomu 12,620 Reputation points Microsoft Vendor

2023-10-10T15:38:55.4433333+00:00
@Ram Babu Thank you for reaching out with your question. When the structure of the source table changes, you can modify your ADF pipeline to handle schema changes by using dynamic schema mapping. This allows you to map the source and target columns dynamically based on the schema of the source and target tables at runtime. You can also use the ADF schema drift feature to detect any schema changes in the source or target tables and automatically update the schema mapping.

When a pipeline experiences a failed activity, you have the option to automate the process of rerunning from the failed activity. You can utilize the Retry option available within each activity to achieve this. In the pipeline, you can configure any activity to retry multiple times in the event of a failure. (Please refer to the screenshot for more details).

To drop the table and auto create the table, you can use the stored procedure activity in Synapse analytics. It can create a stored procedure that drops the table and creates a new table and then execute this stored procedure.
Relevant links:

Copy activity dynamic mapping

Schema drift in ADF

I hope this information helps you!
Ram Babu 50 Reputation points

2023-10-11T09:47:39.2266667+00:00

Hi, as earlier i have mention that source is salesforce and target is azure synapse dedicate pool. so data flow doesn't allow out of azure right? then how can i use schema drift in the pipeline. we are using foreach activity to iterate the tables then how can i update schema mapping.. please advice

Thanks
Smaran Thoomu 12,620 Reputation points Microsoft Vendor

2023-10-11T15:49:50.27+00:00
@Ram babu In this situation it doesn't support Salesforce as a source. However, you can still use the schema drift by using copy Activity to copy data from Salesforce to blob storage or data lake storage.

Use ForEach Activity to iterate over the tables stored in your Azure storage. It dynamically generates table names and passes them to subsequent pipeline activities. Make use of the Stored Procedure Activity to handle table changes in Azure Synapse Analytics.

Create a stored procedure to drop and recreate tables with the correct schema. Execute this stored procedure via the Stored Procedure Activity.

Finally, the Copy Activity is employed to move data from your storage account to Azure Synapse Analytics. Dynamic mapping within the Copy Activity allows for on-the-fly column mapping based on source data, helping to address any column mismatches between source and target tables.

I hope this guidance helps you resolve your query.
Smaran Thoomu 12,620 Reputation points Microsoft Vendor

2023-10-13T04:27:25.6533333+00:00

@Ram Babu We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

pipeline should re-run even if tables are failed due to column mismatch