Unexpected result type of schema evaluation when trying to poulate new CDS table with Data from on premise SQL DB
I am pretty new to CDS. I have built a number of powerapps using Sharepoint lists as the datasource but recently built an powerapp that references data in an onprem SQL database table. Everything works but data load times are poor.
I am trying to setup a copy of the SQL table in CDS, initially for testing but then later as a live environment with changes in the onprem SQL table updating the CDS table automatically.
I am failing at step one at the moment. I can go into either "Data" or "Dataflows" from the powerapps portal, I can create a table in CDS (just has one column at the moment) and I can follow through some of the process i.e I can see the DB i want to connect to and can select the required table as well as select "Transform Data" and select all columns. When I click "Next", after about 20 seconds, I get the following error:
Unexpected result type of schema evaluation. Expected: "Table". Actual: "Exception".
I have searched the internet for this error but cannot find anything useful. I would be eternally grateful if anyone can shed any light on why I might be getting this error and more importantly, how to get past it!