Azure Synapse: Dataflow runs in preview but not from Pipeline

Question

Hi

I've built a dataflow which leverages the serverless built-in sql to query some csv files. The query does work since I can see the correct data when I select the preview in the dataflow editor.
This is true for the whole dataflow, the preview at the sink is still correct and there are no errors.

If I try to run the pipeline it fails whit this error though:

{"message":"Job failed due to reason: at Source 'source1': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'HADOOP'.. Details:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'HADOOP'.\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)\n\tat shaded.msd","failureType":"UserError","target":"Dataflow1","errorCode":"DFExecutorUserError"}  

Can someone put me in the right direction?

Thanks

Answer 1

Hi @GB ,

Welcome to Microsoft Q&A platform and thankyou for posting your query here.

As I understand your issue, you are trying to load data from .csv file present in ADLS to dedicated sql pool using Data flow where you have pointed your source to Serverless SQL pool and trying to fetch data from the csv file. It is showing the expected result when you preview the data, however, it fails to execute pipeline after calling the dataflow. Please let me know if my understanding about your query is incorrect.

The cause of this issue is that you are trying to load staged data in the process. While using Synapse serverless pool, When you select 'enable staging' in the Source, you face the following error: shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'IDENTITY'.

Serverless pool doesn't support staged data loading, so 'enable staging' is not supported.

I tried to reproduce your scenario and was able to troubleshoot it by disabling the staged data loading. Please check the below video for the same:

Please refer to the following document for details: Serverless pool (SQL on-demand) related issues

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators