ADF fails when triggered by api, but succeeds when in debug

Question

ADF fails when triggered by api, but succeeds when in debug

Avila, Sergio (Denver) 20

I have a data factory with multiple pipelines. I have 1 master pipeline that calls other wrapper pipelines. The master pipeline gets triggered by a logic app. I have multiple pipelines that copy data from/to different tables. In the sink section, some of these pipelines fail because it can't find field [xxxx] in the source, but that should not be a problem because the target table has a constraint(default value) on inserts. One of these fields is the Primary key (ID) so this is automatically generated on insert.

Screenshot 2023-07-20 132320

The main problem is that this only happens when the master pipeline gets triggered. It works correctly when we execute the master pipeline in debug mode. Another problem is also that this only seems to happen in a few tables, not all of them.

I checked, and the JSON is identical to other working pipelines. I also checked the constraints on the target failing tables and everything is correct.

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-21T15:42:44.7866667+00:00

Hi Avila, Sergio (Denver) ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding regarding your query, you are witnessing Invalid column name error at the sink side and ADF pipeline is failing. You also mentioned that this issue is only occurring when the pipeline is triggered , and it's successfully getting completed when it runs in debug mode. Please let me know if that is not the case.

Kindly try to compare the 'published branch json' with the json you are running in debug mode. Definitely, there should be some difference , since you are mentioning issue is occurring with trigger which means the published pipeline has some misconfiguration. Try checking whether same IR , same linked services configurations are being used in both pipelines or not.

Now , coming to the cause of the error, as it says, the source columnname is not found. Kindly try debugging, whether the column is missing in the source or in case the column orders are changed in the source.

For example: Suppose you ran a pipeline copying data from db1.table1(col1,col2,col3) to db2.table1(col1,col2,col3) . It successfully copied the data , now after some days , source schema is changed (col2 and col3 orders got changed) , so the new table structure is col1,col3,col2. In sink, it is still expecting col2 after col1. So, it will give an error saying col2 is not found in source.

Kindly try debugging by extracting the table schema from both source as well as sink side and share the updates for further assistance. T

Hope it helps. Please accept the answer if it helped. Thankyou
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-24T06:57:48.03+00:00

Hi Avila, Sergio (Denver) ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly share an update if you got a chance to look at the previous response and try the same. Thankyou
Avila, Sergio (Denver) 20 Reputation points

2023-07-24T13:41:12.6+00:00

Hello, I have checked everything, and the pipeline does have the same configuration. The source column should not matter since it's throwing an exception on the Id which is the primary key and is auto generated by the db on row creation.

One more piece of context is that I am deploying the data factory in azure DevOps using terraform scripts

Accepted answer

2 additional answers

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-21T15:42:44.7866667+00:00

Hi Avila, Sergio (Denver) ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding regarding your query, you are witnessing Invalid column name error at the sink side and ADF pipeline is failing. You also mentioned that this issue is only occurring when the pipeline is triggered , and it's successfully getting completed when it runs in debug mode. Please let me know if that is not the case.

Kindly try to compare the 'published branch json' with the json you are running in debug mode. Definitely, there should be some difference , since you are mentioning issue is occurring with trigger which means the published pipeline has some misconfiguration. Try checking whether same IR , same linked services configurations are being used in both pipelines or not.

Now , coming to the cause of the error, as it says, the source columnname is not found. Kindly try debugging, whether the column is missing in the source or in case the column orders are changed in the source.

For example: Suppose you ran a pipeline copying data from db1.table1(col1,col2,col3) to db2.table1(col1,col2,col3) . It successfully copied the data , now after some days , source schema is changed (col2 and col3 orders got changed) , so the new table structure is col1,col3,col2. In sink, it is still expecting col2 after col1. So, it will give an error saying col2 is not found in source.

Kindly try debugging by extracting the table schema from both source as well as sink side and share the updates for further assistance. T

Hope it helps. Please accept the answer if it helped. Thankyou
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-24T06:57:48.03+00:00

Hi Avila, Sergio (Denver) ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly share an update if you got a chance to look at the previous response and try the same. Thankyou
Avila, Sergio (Denver) 20 Reputation points

2023-07-24T13:41:12.6+00:00

Hello, I have checked everything, and the pipeline does have the same configuration. The source column should not matter since it's throwing an exception on the Id which is the primary key and is auto generated by the db on row creation.

One more piece of context is that I am deploying the data factory in azure DevOps using terraform scripts

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Avila, Sergio (Denver) ,

Glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Elaborating your answer, it seems there might be some mismatch in the explicit mapping between source and sink schema under tabular translator. For handling data load for multiple tables , instead of mapping each columns, we could also use Dynamic Column mapping in Copy Activity

User's image

If you want to add some more details, please feel free to add it in the below comment. Kindly accept the answer by clicking on Accept answer button. Thankyou

Avila, Sergio (Denver) 20 Reputation points

2023-07-25T14:21:02.18+00:00

In my case, I had to remove the whole Tabular Translator because I used Dynamic mapping, but having this still was causing problems.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-25T15:16:42.6633333+00:00

Avila, Sergio (Denver) ,

Thankyou for sharing additional details. Appreciate your effort.

Answer 2

Avila, Sergio (Denver) 20

Hello @AnnuKumari-MSFT , I have checked the configuration to other working pipelines, and everything is exactly the same. the missing column coming from the source should not matter since it is throwing an error on the target's table primary key which auto populates on creation.

One more piece of context is that I am deploying the ADF using Terraform scripts in Azure DevOps

Answer 3

Avila, Sergio (Denver) 20

@AnnuKumari-MSFT I figured out the problem. The sink section was failing because of the Tabular Translator.

Share via

ADF fails when triggered by api, but succeeds when in debug

2 additional answers

Your answer