CODEWITHSUNDEEP
azure-synapse
Konrad
Konrad

Reputation: 161

Migrating data from CSV file on Azure Blob Storage to Synapse Analytics (serverless pool)

I have a problem running a pipeline (migration from CSV file stored on Azure Data Factory, to Synapse Analytics) in the Azure Data Factory. It worked fine with dedicated pool, but i can't get it to work with built-in serverless pool.

I created run-once pipeline on adf.azure.com with UI creator. On the "Source Data Store" tab i choose Source type: Azure Blob Storage, then i choose appropriate connection, i press browse to choose desired file, i leave "Recursively" option on, i press next.

The next tab is "File format settings", here I choose Advanced options, and changed Escape character from backslash to double quote. I press next, there's a tab: "Destination data store", i choose target type: Azure Synapse Analytics and then i choose connection. I specified target table name.

On the next tab there's a column mapping, i unchecked type conversion, in the next tab (Copy Data tool settings) I selected Polybase as a copy method.

I enabled staging blob, selected linked Azure Storage service and blob container, then I Pressed next and finished creating a pipeline.

The error message that I received:

Operation on target Copy_ky9 failed: ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'HEAP'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'HEAP'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'HEAP'.,},],'

Since I used a UI for creating a pipeline, I don't know how to check the syntax, although I guess that it internally generates some command, I couldn't find an option to preview it and fix it's syntax.

Upvotes: 0

Views: 1001

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

A dedicated SQL pool in Azure Synapse Analytics has built-in storage; you can load data to a table in a dedicated SQL pool. A Serverless SQL pool has no storage. It is just a metadata layer for views over files in storage; It can read and write files in Azure storage.

I would stop having ADF load it and just build a view in Synapse Serverless SQL.

Upvotes: 0

Related Questions