Retaining empty strings when using POLYBASE to copy data from Azure SQL db to Azure Synapse

0
votes

I am copying data from an Azure SQL database into Azure Synapse using the Copy Data activity. I am attempting to use Polybase as the copy method. This works successfully on most tables but I have a source and destination table with the following columns:

Fee DECIMAL(18,2) NULL,
FeeReason VARCHAR(500) NOT NULL

For reasons beyond my control, I am unable to change the columns to allow NULLs in the FeeReason column. In the table, if Fee is NULL, then FeeReason is an empty string.

When I try and copy the table from Azure SQL DB into Synapse I get an error for the Fee column when that value is NULL:

ErrorCode=FailedDbOperation, ......HadoopSqlException: Error converting data type VARCHAR to DECIMAL.....Detailed Message=Empty string can't be converted to DECIMAL.....

and the solution to this problem is to set the "Use Type Default" setting to false as per loading-to-decimal-column

However, making this change results in the FeeReason value to be NULL in Synapse when the value is an empty string in the source (Azure DB). Is there anyway I can set "Use Type Default" set to False for decimal columns only?

I have a terribly dirty solution to this which involves creating a BIT column in the source database called FeeReasonIsEmpty and flag records where the FeeReason is actually an empty string and once the data has been copied over, I can just update the NULL values to empty strings where relevant. I want to avoid this if possibly because in reality I have multiple tables with multiple decimal columns and multiple varchar/nvarchar columns with empty strings that need to be retained. This dirty solution would mean I'd need to create an "IsEmpty" column for each of those and I don't really want to do that. Thank-you.

1
azure-sql-databaseazure-data-factoryazure-data-factory-2azure-synapsepolybase

1 Answers

1
votes

Unfortunately, we cannot control UseTypeDefault setting to only specific columns at this moment.

To avoid your issue, at this moment you must go with the option of bit column in the source dataset. As far I tried to find solutions and till now that’s the only options seem to create IsEmpty columns for respective columns.

Note: Currently, PolyBase in ADF accepts only the same number of columns as in the target table: Columns with default values

I would encourage you to add this case as feedback item on below link. Product team will closely monitor feed backs there and consider them for future releases: FeedBack