Delimiter Issue in copy activty

Question

Hello Team,

I have designed the pipeline to copy the data from On premises--Blob Storage--->Dedicated sql pool.

I have set the delimiter to copy the file to

On Premises-->Azure blob storage:

I have set the delimiter to copy the file to

Azure blob storage-->Dedicated Sql Pool

And in copy Activity I selected the Polybase has option.I am getting error :

ErrorCode=PolybaseOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse. Operation: 'Polybase operation'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Not enough columns in this line.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107090,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107090,State=1,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Not enough columns in this line.,},],'

I have verified all the columns are same from the blob storage and even the data type i have mentioned [nvarchar] (max) for all the columns

Please advise.

Thanks

Rk

Answer 1

Hi Rohit Kulkarni ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding, you are trying to load the data from on-prem to blob and then to ded SQL pool using copy activity in Synapse pipeline.

It might be the case that any of the columns is containing data having comma separated value which might be treated as another new column using comma as the delimiter set. For example: "Pune,India" might be treated as two columns if not handled correctly.

Kindly try verifying if the data landed in blob is enclosed within double quotes or single quotes and use the same as quote character for the source dataset when you are copying from blob to dedSQLpool.

Alternatively, there might be a column with new line character in it and polybase doesn't supporting column with new line character. Try loading using bulk insert or copy option and ensure you using correct escape character as well.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou.