CODEWITHSUNDEEP
azureazure-synapseazure-data-lake-gen2azure-data-factory
Salvatore Nedia
Salvatore Nedia

Reputation: 352

Azure Data Factory Exception while reading table from Synapse and using staging for Polybase

I'm using Data Flow in Data Factory and I need to join a table from Synapse with my flow of data. When I added the new source in Azure Data Flow I had to add a Staging linked service (as the label said: "For SQL DW, please specify a staging location for PolyBase.")

So I specified a path in Azure Data Lake Gen2 in which Polybase can create its tem dir.

Nevertheless I'm getting this error:

{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Source 'keyMapCliente': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: CREATE EXTERNAL TABLE AS SELECT statement failed as the path name 'abfss://[email protected]/Raw/Tmp/e3e71c102e0a46cea0b286f17cc5b945/' could not be used for export. Please ensure that the specified path is a directory which exists or can be created, and that files can be created in that directory.","Details":"shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: CREATE EXTERNAL TABLE AS SELECT statement failed as the path name 'abfss://[email protected]/Raw/Tmp/e3e71c102e0a46cea0b286f17cc5b945/' could not be used for export. Please ensure that the specified path is a directory which exists or can be created, and that files can be created in that directory.\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.jd"}

The following are the Azure Data Flow Settings:

enter image description here

this the added source inside the data flow:

enter image description here

Any help is appreciated

Upvotes: 0

Views: 1353

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

I have reproed and was able to enable stagging location as azure data lake Gen2 storage account for polybase and connected synapse table data successfully.

  • Create your database scooped credentials with azure storage account key as secret.
  • Create an external data source and an external table with the scooped credentials created.

In Azure data factory:

Enable staging and connect to azure data lake Gen2 storage account with Account key authentication type.

enter image description here

In the data flow, connect your source to the synapse table and enable staging property in the source option

enter image description here

Upvotes: 0

Related Questions