How to fix "Job failed due to reason: at Sink 'DWSink': Cannot create a global temporary table" in ADF data flow?

Question

Hi Team,

I created a pipeline to move JSON files from blob storage into a data warehouse table using a data flow. The data flow worked fine when tested individually. However, when I added the data flow to the pipeline and ran it, I encountered an error as below

Could you take a quick look at the screenshot and let me know if you have any advice on how to fix this? Your insights would be really helpful.

Thanks a lot!

Lucy

Operation on target Data flow1 failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'DWSink': Cannot create a global temporary table.","Details":"shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Cannot create a global temporary table.\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1673)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)\n\tat sh"}

Answer 1

Hello Ovindu Wijethunge,

Welcome to the Microsoft Q&A forum.

From your screenshot I see your sink is "Data warehouse" but you are using SQL DB linked service. This is causing the issue. If it is synapse, you need to use "Synapse linked service"

I hope this helps. Please let me know if you have any further questions.