ADF - Copy activity - error while loading parquet file from Data lake to Azure SqlServer

Question

I'm trying to copy/insert parquet files from data lake to Azure sql server using copy activity. Files without date field copied successfully. But files having date field throwing below exception without any column details. Not sure where the issues is. Parquet file is written to data lake by databricks with date type casted properly. Can you help me to resolve the issue?

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Data.Governance.Plugins.Core.TypeConversionException,Message=Exception occurred in type conversion： .,Source=Microsoft.DataTransfer.ClientLibrary,'

TraceComponentId: TransferTask TraceMessageId: TransferRunFailed @logId: Error FunctionName: Execute jobId: XXX activityId: XXX eventId: TransferRunFailed message: Copy failed with error: 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,StackTrace= at Microsoft.DataTransfer.ClientLibrary.SqlDataWriter.HandleBulkInsertException(Exception ex, IDataReader reader) at Microsoft.DataTransfer.ClientLibrary.SqlDataWriter.BulkInsertMultipleBatches(IDataReader reader) at Microsoft.DataTransfer.ClientLibrary.SqlDataWriter.<>c__DisplayClass159_0.<WriteAsync>b__0() at System.Threading.Tasks.Task1.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DataTransfer.ClientLibrary.SqlDataWriter.<WriteAsync>d__159.MoveNext(),''Type=Microsoft.Azure.Data.Governance.Plugins.Core.TypeConversionException,Message=Exception occurred in type conversion： .,Source=Microsoft.DataTransfer.ClientLibrary,StackTrace= at Microsoft.DataTransfer.Runtime.TypeConversion.LogicalTypeConverterFactory.CreateTypeConverter(IDictionary2 properties) at Microsoft.DataTransfer.Runtime.TypeConversion.TypeConverterManager.CreateConverter(IDictionary`2 properties) at Microsoft.DataTransfer.Runtime.TypeConversion.GeneralTypeConversionProvider.GetTypeConverter(String columnName) at Microsoft.DataTransfer.Runtime.TypeConversion.TypeConversionDataReader.Read() at Microsoft.DataTransfer.ClientLibrary.AggregateDataReader.Read() at Microsoft.DataTransfer.ClientLibrary.TelemetryDataReader.Read() at System.Data.SqlClient.SqlBulkCopy.ReadFromRowSourceAsync(CancellationToken cts) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.DataTransfer.Common.Shared.HelperMethod.ExecuteWithTimeout(Action action, TimeSpan timeout, String timeoutErrorMessage) at Microsoft.DataTransfer.ClientLibrary.SqlDataWriter.BulkInsertMultipleBatches(IDataReader reader),'

Answer 1

@Dinesh Gopalakrishnan Thanks for using Microsoft Q&A forum and posting your query.

I agree that there is no concrete info about the root cause of the issue from the error message you have shared. But for sure something related to data type conversion issue between source parquet data type v/s SQL column data type.

To identify the actual root cause of the issue, could you please try below testing and let me know how it goes.

1. Try to insert one record into the sink SQL table using SSMS and see if that provides more detailed information about the actual problem. OR
2. Please try setting the "Fault Tolerance" property in Copy Data settings to "Skip incompatible rows" and log all the incompatible rows to a file and start analyzing on the column mentioned in the error message logged.

Ref doc: Fault Tolerance in Copy Activity

Hope this info helps to further troubleshoot the problem. Do let me know how it goes.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.db806)