Dataflow Gen 2 to Populate the SQL Server tables issue

[tan_thiamhuat]

I would like to check if anyone has used Dataflow Gen 2 to populate tables inside SQL Server database?

I have that table drop in SQL Server, create a brand new table inside SQL Server database, which I check is of zero row.

When I run the Dataflow Gen 2 to populate that table, which I check from it with its 

Table.RowCount(TableToInsert), it shows a value of 159128. However, when I do a count inside that SQL Server for that table, it displays a totally different value. 

And I repeat the whole process a few times, it is alway repeatable, and give that wrong count (same value) inside SQL Server.

For the above population, whether I choose the Append or Replace option, both would not give me the correct count inside SQL Server. And the whole process, I always start new with dropping the table.

[Anonymous]

Hi @tan_thiamhuat,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like facing a discrepancy between the row count shown in your Dataflow Gen2 and the actual data inserted into your SQL Server table, even after recreating the table. Based on the behavior you described and following up with the point mentioned by @nilendraFabric about "Table.RowCount", here are a few more possible causes and steps you can follow.

* It can be possible that few rows were skipped because there is a mismatch for the data type. "NULL" constraint violations, or other SQL Server-level rules. We recommend checking the table schema to ensure it fully matches the structure and constraints expected by the incoming data.

* After running the Dataflow, please review the run history and inspect the output step logs. See if any warnings or partial success messages appear, which might indicate skipped rows.

I would also take a moment to thank @nilendraFabric, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[nilendraFabric · Accepted solution]

Hi @tan_thiamhuat 

The count reported by `Table.RowCount(TableToInsert)` reflects the number of rows in the dataflow’s in-memory table, not necessarily what is committed to SQL Server. If there is a failure during the write operation (due to constraints, timeouts, or connectivity issues), not all rows may be inserted, 

[tan_thiamhuat]

I have more than double the rows of 159128 in SQL Server.