locked
Azure Data Factory copy csv to SQL cannot convert empty data RRS feed

  • 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","","","","","","","","","","",""

    Any clue would be much appreciated.

    Monday, October 19, 2020 9:28 AM

All replies

  • Hi Benzy_S,
    For questions about Azure, it is recommended to ask the question on the Microsoft Q&A forum and you can get more professional answer.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 20, 2020 6:13 AM