Data Factory copy csv to SQL cannot convert empty data

Question

Encountered below various errors caused by empty data when building a very basic Copy Data task from File Sharing to Azure SQL:

ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'EndDate' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). Additional info: String was not recognized as a valid DateTime.

And here is another one I believe caused by the same reason:

ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'ContractID' from type 'String' (precision:, scale:) to type 'Guid' (precision:255, scale:255). Additional info: Unrecognized Guid format.

All I need is to treat empty data as NULL when copying to SQL Tables. The only option I have found is "Null value" in my CSV dataset; and it is set to nothing as default.

Below is the code of CSV dataset:

{
    "name": "CSV",
    "properties": {
        "linkedServiceName": {
            "referenceName": "CSV",
            "type": "LinkedServiceReference"
        },
        "parameters": {
            "FileName": {
                "type": "string"
            }
        },
        "annotations": [],
        "type": "DelimitedText",
        "typeProperties": {
            "location": {
                "type": "AzureFileStorageLocation",
                "fileName": {
                    "value": "@dataset().FileName",
                    "type": "Expression"
                },
                "folderPath": "output"
            },
            "columnDelimiter": ",",
            "escapeChar": "\\",
            "firstRowAsHeader": true,
            "quoteChar": "\""
        },
        "schema": []
    }
}

The csv file does use double quotation marks as the qualifier. And those empty data in source files look like this:

"b139fe4d-f48a-4158-8196-a43500b3bf02","19601","Bar","2015/02/02","","","","","","","","","","",""

Joseph Xu Joseph Xu · Accepted Answer · 2020-10-19T02:45:21

Due to the Copy Activity cann't process the empty value, so we need to use Data Flow to Convert the field to NULL value.

Here is my test using your example:

I created a table in Azure SQL

Create table TestNull(
    Column1 UNIQUEIDENTIFIER null,
    Column2 varchar(50) null,
    Column3 varchar(60) null,
    Column4 DateTime null,
    Column5 varchar(50) null,
    Column6 varchar(50) null
)

In ADF, We can use DerivedColumn to convert the empty value to NULL value. So we can use the expression iifNull(Column_1,toString(null())) to judge the if the field is empty, if so it will be replaced with a NULL value.

In the sink, we should set the mapping.

It will insert NULL value into the table.

Data Factory copy csv to SQL cannot convert empty data

1 Answers