Debugging bulk copy using for loop

Question

Debugging bulk copy using for loop

Siddhanth Shetty 66

I am working on a task that requires me to copy 348 tables from a SQL server to blob storage and then from blob storage to Azure Synapse SQL pool which is then visualized using Power BI. I made use of the 'Lookup' and 'ForEach' activities. When I run the process I see 698 items in the output. Most of them succeeded but around 4 copy activities from Blob storage to Azure Synapse have failed. I have a few questions regarding this:

How do I find out which Table or step in the ForEach loop has caused the error?
What exactly happens when one step fails because when I check the number of tables in the source and destination matches. I have not selected any fault tolerance options. What is the default behavior?

I appreciate any help! Thank you!

HarithaMaddi-MSFT 10,146 Reputation points

2020-10-07T14:40:25.21+00:00
Hi @Siddhanth Shetty ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

To track the tables that failed to load, we can use failure connector to the copy activity inside foreach loop and append the failed table/file name into a array variable using append activity. After the loop, this variable can be copied into a file in blob that can be used to browse through failed tables.

If you are using "auto create table" option for the sink, before loading the data, the table will be created by the data factory and that can be one reason you see tables exist even for failed copy activities. If the foreach loop is running in parallel, failure of one loop does not prevent other loops and it will not load data into that table.

Hope this helps! Please let us know if the details are not aligned with the requirement and we will be glad to assist further.
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-08T07:15:14.37+00:00

Hi @Siddhanth Shetty ,

We have not received a response from you. Please let us know if above response helped clarify the question. Otherwise, let us know and we will continue to engage with you on the query.
Siddhanth Shetty 66 Reputation points

2020-10-09T12:52:15.297+00:00

Thank you for your response Haritha. I have created a variable and append to it every time there is a failure in the copy activity. How do I then write this append variable into a file in the blob storage?

Accepted answer

0 additional answers

Your answer

HarithaMaddi-MSFT 10,146 Reputation points

2020-10-07T14:40:25.21+00:00

Hi @Siddhanth Shetty ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

To track the tables that failed to load, we can use failure connector to the copy activity inside foreach loop and append the failed table/file name into a array variable using append activity. After the loop, this variable can be copied into a file in blob that can be used to browse through failed tables.

If you are using "auto create table" option for the sink, before loading the data, the table will be created by the data factory and that can be one reason you see tables exist even for failed copy activities. If the foreach loop is running in parallel, failure of one loop does not prevent other loops and it will not load data into that table.

Hope this helps! Please let us know if the details are not aligned with the requirement and we will be glad to assist further.
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-08T07:15:14.37+00:00

Hi @Siddhanth Shetty ,

We have not received a response from you. Please let us know if above response helped clarify the question. Otherwise, let us know and we will continue to engage with you on the query.
Siddhanth Shetty 66 Reputation points

2020-10-09T12:52:15.297+00:00

Thank you for your response Haritha. I have created a variable and append to it every time there is a failure in the copy activity. How do I then write this append variable into a file in the blob storage?

Answer 1

HarithaMaddi-MSFT 10,146

Hi @Siddhanth Shetty ,

Thanks for sharing more details.

Below GIF explains the approach that can be used to copy the failed tables from an array variable into a file in blob. Source to copy activity can be a dummy file with 1/few rows as we will not be loading any source data but we will use variable to be copied into the blob as below.

Hope this helps! Please let us know for any queries and we will be glad to assist further.

Siddhanth Shetty 66 Reputation points

2020-10-09T14:26:00.653+00:00

Thank you so much! This is a huge help!

Share via

Debugging bulk copy using for loop

0 additional answers

Your answer