Why is data not getting inserted in DB after the data flow task is successful in ADF

Quadri, Mumin Ahmed 5

I have a dataflow task which is supposed to insert data in a DB. Im using SQL Server as DB and am trying to insert data in a table. When we execute the pipeline on ADF which has the required data flow task, we can see that the pipeline executes successfully. Also, we see all data to be inserted in Data Preview of sink in Data Flow task. However, when the pipeline completes successfully, we do not see any data inserted in DB. In the activity runs for this pipeline, we can see Rows Written is shown as 0. Also, when we check the 'Stages' link in the Activity runs, we see one of the steps in data flow is cached. Please find below the relevant screenshots User's image

User's image

Bhargava-MSFT 28,951 Reputation points Microsoft Employee

2024-02-15T21:12:05.68+00:00

Hello Quadri, Mumin Ahmed,

Welcome to the Microsoft Q&A forum.

from the issue description, it seems like the issue is with the configuration of the data flow task or the destination server.

Can you please check your destination SQL server is configured to perform the inserts of the login account that you are using.

Can you also check if there are any issues with the data types(mappings of the columns)?
Quadri, Mumin Ahmed 5 Reputation points

2024-02-16T10:54:12.6533333+00:00

I checked the SQL Server account that I'm using for this data flow & it has permission to insert data, I inserted data in SSMS manually. Also, my mapping is correct.
Quadri, Mumin Ahmed 5 Reputation points

2024-02-16T17:16:24.8733333+00:00

There is 1 observation that I would like to point out. If you see in below screenshot, only the first step i-e 'SrcDocument' shows that some data was retrieved. This is the 1st step in data flow. The rows retrieved shows count as 144k, but it shows as cached data. Is that causing some issue? Is there any caching that happens on ADF automatically? I am not using any caching settings in my source or sink configurations.

Also, when i check data flow diagnostics in Activity runs, I see below for SrcDocument Diagnostics:

This clearly shows that 144387 rows are picked up from source. Interestingly, in all successive steps including the last sink step, we dont see stream information like above. We see below for all successive steps in data flow:

Please note that there is 1 more thing I tried for testing purposes. I inserted data from source to sink in the same table using a data flow with just 2 steps, first step for source & other for sink. I was able to insert data in the same table. And in diagnostics I could see rows written to table and there was no cached data that was showing in Transformations. So, does that mean there is some step in my actual data flow which is causing this issue? Please let me know if you need any further details on this. This is turning into a blocker for us. I really appreciate your support and help on this. Thanks!
Bhargava-MSFT 28,951 Reputation points Microsoft Employee

2024-02-16T21:21:04.1466667+00:00

Hello Quadri, Mumin Ahmed,

After further research I found out, it seems that the data preview in your data flow task is showing cached data instead of the latest data from the source.

This could be causing the issue where no data is being inserted into the SQL Server database.

There is a known issue where the ‘Refetch from source’ feature in data flow data preview is not working as expected and only showing the cached data

Post: https://video2.skills-academy.com/en-us/answers/questions/1389606/clearing-data-preview-cache-in-data-flow

As a work around, could you change the source dataset to a different one and do a preview and come to source settings and repoint to your actual dataset to preview the latest data from your source file. This should refresh the data preview and show the latest data from the source.

I hope this helps. Let me know if you have any further questions.
Quadri, Mumin Ahmed 5 Reputation points

2024-02-19T11:06:32.4433333+00:00

Thanks for responding. I did try the suggested workaround, but it does not resolve the issue. I pointed out to a separate dataset and did data preview, which showed different data. Afterwards, I changed my source dataset back to original one and did data preview again, which showed my original data. I also renamed the source data step from 'SrcDocument' to 'SourceDataset' as shown in below screenshot, but it still shows data as cached.

Also, there is one more thing I tried. I used triggers to run the pipeline also apart from data flow debug, but it still shows dataset as cached. If this issue is due to caching, should it happen when we use triggers? How will we ensure that when this pipeline runs in Stage or Production, we won't see same behavior. Since, I observed the same issue when using triggers for pipeline in dev, it will most likely occur in all other environments? Also, in my other pipeline, I tried creating some similar test scenarios & checking the behavior. I could see same caching issue, but in middle of data flow task as shown below:

This time caching is shown on a step inside data flow where we do full outer join. Please let us know if there is any fix for this. Appreciate your help!
Quadri, Mumin Ahmed 5 Reputation points

2024-02-20T14:31:14.94+00:00

Is there any update on this? Thanks!
Bhargava-MSFT 28,951 Reputation points Microsoft Employee

2024-02-22T23:19:07.94+00:00

Hello Quadri, Mumin Ahmed,

sorry for the delayed response. My understanding is that the caching issue observed during the full outer join step might be related to specific transformation logic. Troubleshooting without direct access to your data and data flow activities can be challenging. As the next step, I recommend opening a support case so that a support engineer can further investigate the issue in a screen-sharing session. If you don’t have a support plan, please let me know, and I can enable a one-time free support request to address this case. Looking forward to hearing from you.
Christian Canuto 0 Reputation points

2024-07-09T15:44:01.51+00:00

@Quadri, Mumin Ahmed
Hello,
Do you have any update regarding this issue?
I'm facing the exact same problem, and I remarked that it happens when I use a Conditional Split component or a Filter component. I can see that the data flows correctly through all the component and arrives at the sink (where I can see the number of rows), but when I check my parquet file it is empty.
But it is not a permanent behavior. I can have the case where the data inserts correctly on the destination (if I keep on debugging the same data flow over and over).

Thank you in advance for your feedback.

2 answers

Quadri, Mumin Ahmed 5 Reputation points

2024-07-09T15:53:38.5833333+00:00

I was able to resolve this issue a month back. I had raised a ticket with MSFT on this but they couldn't find the root cause. For my case, the issue was with the Integration Runtime configuration. The AutoresolveIntegrationruntime used 4+4 cores by default. I created a new IR with custom compute size, compute type as 'Memory Optimized' and core count as 16+16 cores which resolved issue for me. In ADF, go to Manage-> Integration Runtime. Here, u can modify existing IR or create new IR.
Please sign in to rate this answer.

1 person found this answer helpful.
Christian Canuto 0 Reputation points

2024-07-10T08:36:50.93+00:00

@Quadri, Mumin Ahmed
Thank you for the feedback. I'll try this approach and see if it works for me also!

Bhargava-MSFT 28,951 Reputation points Microsoft Employee

2024-07-10T15:36:46.5333333+00:00

Hello Quadri, Mumin Ahmed

I'm 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.
Sign in to comment
Bhargava-MSFT 28,951 Reputation points Microsoft Employee

2024-07-10T15:36:51.6366667+00:00

Hello Quadri, Mumin Ahmed

I'm 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 am reposting your solution in case you'd like to "Accept " the answer.

Issue:

Data is not getting inserted in DB after the data flow task is successful in ADF

Solution:

the issue was with the Integration Runtime configuration. The AutoresolveIntegrationruntime used 4+4 cores by default. The issue was resolved after creating a new IR with custom compute size, compute type as 'Memory Optimized' and core count as 16+16 cores.

How to modify existing IR or create new IR.

In ADF, go to Manage-> Integration Runtime. from here we can modify or create new IR

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Why is data not getting inserted in DB after the data flow task is successful in ADF

2 answers