Copy Activity Blob to Snowflake Trigger on demand failure but debug successful

Question

Copy Activity Blob to Snowflake Trigger on demand failure but debug successful

Cathy Lee 26

I'm getting the following error when executing a pipeline with an on demand trigger but when I debug it's successful.

ErrorCode=AzureBlobOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=Blob operation Failed.
ContainerName: , path: processed/20210331/FactTickets.parquet.,
Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=The remote server returned an error: (403) Forbidden.,Source=,
''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=The remote server returned an error:
(403) Forbidden.,Source=Microsoft.WindowsAzure.Storage,''Type=System.Net.WebException,Message=
The remote server returned an error: (403) Forbidden.,Source=Microsoft.WindowsAzure.Storage,'

Thanks,

Cathy

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-03-31T15:10:27.11+00:00

Hi @Cathy Lee ,

Welcome to Microsoft Q&A and thanks for reaching out.
This usually happens when we make changes to the pipeline and if the changes haven't published. Could you please confirm if the changes have been published before using Trigger now button? Is your ADF associated with a Code repository or in default Data Factory mode? From the error message it seems the issue is while accessing the storage account.

Please confirm what is the Authentication method used to establish the storage connection? Are you using Azure Key Vault or the connection string in Azure Storage linked service connection? In case if you are using Service principal or Managed Identity to access the storage account Please make sure you have granted the Storage Blob Data Reader role.

Please let us know how it goes.

Thanks.
Cathy Lee 26 Reputation points

2021-03-31T17:32:25.21+00:00

I published all the changes but trigger on demand pipeline still failed.
Our code is linked to our Git repository.
Yes it looks to be an issue with the Storage account but I'm unsure why it's an issue when triggered and not during debugging. We are using SAS URI authentication method as this is required when populating Snowflake tables using Snowflake LS in the copy command. We are using the AKV in the linked service connection.
Cathy Lee 26 Reputation points

2021-04-01T15:59:19.97+00:00

@KranthiPakala-MSFT any information that might help me proceed? Struggling to find anything insightful on line.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-02T00:33:01.953+00:00

Hi @Cathy Lee ,

Thanks for your response and details. As your ADF is associated with GIT repository and you are using the AKV in the Linked service there is a possibility that the pipeline trigger could fail if the changes were not published on the ADF publish branch. To make sure if this is the reason, could you please try using the connection string instead of AKV in the linked service and save the changes. When you use the connection string and save the LS settings, the changes are immediately published to the ADF Publish branch.
Could you please try triggering your pipeline with Connection string instead of AKV and see how it goes?

If triggering pipeline with connection string works fine then it confirms that the LS AKV changes are not published to ADF publish branch and they need to published to ADF publish branch to resolve the issue. .

Let us know how it goes.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-05T22:18:44.613+00:00

Hi @Cathy Lee ,

Following up to see if the above information was helpful to resolve your issue? Do let us know if you still have further query.

Thank you
Cathy Lee 26 Reputation points

2021-04-06T18:59:33.44+00:00

@KranthiPakala-MSFT apologies for the delay getting back to you, I've been off the past few days. It works using the connection string instead of the AKV. So when using AKV I publish it in ADF so why isn't that publishing properly? Is it a Dev Ops issue? I have a call with Aravind Nuthalapati tomorrow to walk through what's happening as I was told to open a support ticket through our Azure portal in addition to this post.

Accepted answer

0 additional answers

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-03-31T15:10:27.11+00:00

Hi @Cathy Lee ,

Welcome to Microsoft Q&A and thanks for reaching out.
This usually happens when we make changes to the pipeline and if the changes haven't published. Could you please confirm if the changes have been published before using Trigger now button? Is your ADF associated with a Code repository or in default Data Factory mode? From the error message it seems the issue is while accessing the storage account.

Please confirm what is the Authentication method used to establish the storage connection? Are you using Azure Key Vault or the connection string in Azure Storage linked service connection? In case if you are using Service principal or Managed Identity to access the storage account Please make sure you have granted the Storage Blob Data Reader role.

Please let us know how it goes.

Thanks.
Cathy Lee 26 Reputation points

2021-03-31T17:32:25.21+00:00

I published all the changes but trigger on demand pipeline still failed.
Our code is linked to our Git repository.
Yes it looks to be an issue with the Storage account but I'm unsure why it's an issue when triggered and not during debugging. We are using SAS URI authentication method as this is required when populating Snowflake tables using Snowflake LS in the copy command. We are using the AKV in the linked service connection.
Cathy Lee 26 Reputation points

2021-04-01T15:59:19.97+00:00

@KranthiPakala-MSFT any information that might help me proceed? Struggling to find anything insightful on line.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-02T00:33:01.953+00:00

Hi @Cathy Lee ,

Thanks for your response and details. As your ADF is associated with GIT repository and you are using the AKV in the Linked service there is a possibility that the pipeline trigger could fail if the changes were not published on the ADF publish branch. To make sure if this is the reason, could you please try using the connection string instead of AKV in the linked service and save the changes. When you use the connection string and save the LS settings, the changes are immediately published to the ADF Publish branch.
Could you please try triggering your pipeline with Connection string instead of AKV and see how it goes?

If triggering pipeline with connection string works fine then it confirms that the LS AKV changes are not published to ADF publish branch and they need to published to ADF publish branch to resolve the issue. .

Let us know how it goes.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-05T22:18:44.613+00:00

Hi @Cathy Lee ,

Following up to see if the above information was helpful to resolve your issue? Do let us know if you still have further query.

Thank you
Cathy Lee 26 Reputation points

2021-04-06T18:59:33.44+00:00

@KranthiPakala-MSFT apologies for the delay getting back to you, I've been off the past few days. It works using the connection string instead of the AKV. So when using AKV I publish it in ADF so why isn't that publishing properly? Is it a Dev Ops issue? I have a call with Aravind Nuthalapati tomorrow to walk through what's happening as I was told to open a support ticket through our Azure portal in addition to this post.

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi @Cathy Lee ,

Thanks for your response. Once you update the Linked service with AKV details in your development/local branch, you will have to create a pull request to merge the changes to Collaboration branch which is master branch.

Once the PR is merged to master branch, you can change the branch to master branch in ADF UI and go to the Snowflake linked service to verify whether changes are merged successfully.

Once you confirm the changes are merged to master branch, then you will have to publish the changes from master branch to adf_publish nothing but you will have to click Publish button from master branch.

This will publish your linked service changes to adf_publish which is nothing but ADF service.
After successfully publishing you can verify if the changes are publish by changing from master branch to Switch to Live mode and then go to Manage hub, and then go to linked services and the check the snowflake linked service configuration.

Hope this clarifies. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-09T17:06:23.363+00:00

Hi @Cathy Lee ,

We still have not heard back from you. Just wanted to check if the above suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-12T16:38:28.13+00:00

Hi @Cathy Lee ,

Just wanted to check if the above info was helpful. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.
Cathy Lee 26 Reputation points

2021-04-12T16:58:51.163+00:00

@KranthiPakala-MSFT I am waiting for the availability of our Dev Ops guy to progress with this. I will update this post when I am done. Thanks.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-12T17:02:18.143+00:00

Thank you for the update. @Cathy Lee !

Have a good day! :)

Share via

Copy Activity Blob to Snowflake Trigger on demand failure but debug successful

0 additional answers

Your answer