I have an ODBC source that I am hoping to migrate to data flow. Currently, it refreshes via .pbix on the service. I can successfully refresh the dataset on Power BI Desktop and on the Service via scheduled refresh of a standard data model. I have tried to configure the exact same DSN to refresh via Data Flow and it is pulling the following error. I have a proof of concept of a smaller table using the same configuration, which succeeds immediately. I have now tried two large tables (5-15M rows) and both return this same error, but work fine in Desktop and via Data Model .pbix refresh on the Service. Any ideas or tips to troubleshoot?
Error: PipelineException: The ODBC driver returned an invalid value. .. RootActivityId = 47520d22-77b3-42c9-95aa-dadae1eab2c1.Param1 = PipelineException: The ODBC driver returned an invalid value. Request ID: 9535876d-cf7a-fc11-28b7-9bf1e3cd2848.
I stumbled upon this post because we were facing the exact same issue than you. We wanted to migrate a dataset that had some ODBC connections to a dataflow. In the dataset it worked perfectly fine, but when we migrated to the dataflow it started showing the error: The ODBC driver returned an invalid value.
After some hours of debugging, we found this value in a date column on our data lake: -0001-11-30 00:00:00.000.
Quite odd if you ask me. We filtered that row in an SQL query within the dataflow and it loaded fine. We did find it curious, though, that it worked in the dataset without an issue, but not in the dataflow, so we checked that row on the older dataset to see what value we saw there, and it was blank. My takeaway of this is that dataset does some data clean on odd dates if it found any, but the dataflow throws an error.
Anyway, I guess you fixed the issue already, but I figured that posting our solution here might help others in the future because this was the only post I found on this subject on Google.