Azure 数据工厂 - 复制活动空值不会更改为 null 错误

Question

我有一个带有 txt 文件的 azure blob。某些列具有空值，因此当它们保存到数据库表中时，它们为 NULL。我可以让它与直接SQL和SSIS ETL包一起使用。

行示例：
1002,100，黄油，盐搅打 黄油，搅打W盐，Y，0,6.38,,,

最后三个假定为空。

当我尝试使用 ADF 时，出现此错误：

复制活动遇到用户错误： ErrorCode=UserErrorInvalidDataValue，'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message=Column "碳水化合物因子"包含一个无效的值"。无法转换" ' 键入 'Decimal'.，Source=Microsoft.DataTransfer.Common，''Type=System.FormatException，Message=Input 字符串格式不正确，Source=mscorlib，'.

FoodDescriptionsAzureBlob：

{
    "name": "FoodDescriptionsAzureBlob",
    "properties": {
        "structure": [
            {
                "name": "NutrientDatabankNumber",
                "type": "Int32"
            },
            {
                "name": "FoodGroupCode",
                "type": "Int32"
            },
            {
                "name": "LongDescription",
                "type": "String"
            },
            {
                "name": "ShortDescription",
                "type": "String"
            },
            {
                "name": "CommonName",
                "type": "String"
            },
            {
                "name": "ManufacturerName",
                "type": "String"
            },
            {
                "name": "Survey",
                "type": "String"
            },
            {
                "name": "ReferenceDescription",
                "type": "String"
            },
            {
                "name": "RefusePercentage",
                "type": "Int32"
            },
            {
                "name": "ScientificName",
                "type": "String"
            },
            {
                "name": "NitrogenFactor",
                "type": "Decimal"
            },
            {
                "name": "ProteinFactor",
                "type": "Decimal"
            },
            {
                "name": "FatFactor",
                "type": "Decimal"
            },
            {
                "name": "CarbohydratesFactor",
                "type": "Decimal"
            }
        ],
        "published": false,
        "type": "AzureBlob",
        "linkedServiceName": "AzureStorageLinkedService",
        "typeProperties": {
            "fileName": "FOOD_DES.txt",
            "folderPath": "gym-nutrition-data/NutrientData/",
            "format": {
                "type": "TextFormat",
                "rowDelimiter": "n",
                "columnDelimiter": "^",
                "nullValue": "",
                "quoteChar": "~"
            }
        },
        "availability": {
            "frequency": "Minute",
            "interval": 15
        },
        "external": true,
        "policy": {}
    }
}

FoodDescriptionsSQLAzure：

{
    "name": "FoodDescriptionsSQLAzure",
    "properties": {
        "structure": [
            {
                "name": "NutrientDatabankNumber",
                "type": "Int32"
            },
            {
                "name": "FoodGroupCode",
                "type": "Int32"
            },
            {
                "name": "LongDescription",
                "type": "String"
            },
            {
                "name": "ShortDescription",
                "type": "String"
            },
            {
                "name": "CommonName",
                "type": "String"
            },
            {
                "name": "ManufacturerName",
                "type": "String"
            },
            {
                "name": "Survey",
                "type": "String"
            },
            {
                "name": "ReferenceDescription",
                "type": "String"
            },
            {
                "name": "RefusePercentage",
                "type": "Int32"
            },
            {
                "name": "ScientificName",
                "type": "String"
            },
            {
                "name": "NitrogenFactor",
                "type": "Decimal"
            },
            {
                "name": "ProteinFactor",
                "type": "Decimal"
            },
            {
                "name": "FatFactor",
                "type": "Decimal"
            },
            {
                "name": "CarbohydratesFactor",
                "type": "Decimal"
            }
        ],
        "published": false,
        "type": "AzureSqlTable",
        "linkedServiceName": "AzureSqlLinkedService",
        "typeProperties": {
            "tableName": "FoodDescriptions"
        },
        "availability": {
            "frequency": "Minute",
            "interval": 15
        }
    }
}

管道：

{
    "name": "NutrientDataBlobToAzureSqlPipeline",
    "properties": {
        "description": "Copy nutrient data from Azure BLOB to Azure SQL",
        "activities": [
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "BlobSource",
                        "treatEmptyAsNull": true
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 10000,
                        "writeBatchTimeout": "60.00:00:00"
                    }
                },
                "inputs": [
                    {
                        "name": "FoodGroupDescriptionsAzureBlob"
                    }
                ],
                "outputs": [
                    {
                        "name": "FoodGroupDescriptionsSQLAzure"
                    }
                ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst"
                },
                "scheduler": {
                    "frequency": "Minute",
                    "interval": 15
                },
                "name": "FoodGroupDescriptions",
                "description": "#1 Bulk Import FoodGroupDescriptions"
            },
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "BlobSource",
                        "treatEmptyAsNull": true
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 10000,
                        "writeBatchTimeout": "60.00:00:00"
                    }
                },
                "inputs": [
                    {
                        "name": "FoodDescriptionsAzureBlob"
                    }
                ],
                "outputs": [
                    {
                        "name": "FoodDescriptionsSQLAzure"
                    }
                ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst"
                },
                "scheduler": {
                    "frequency": "Minute",
                    "interval": 15
                },
                "name": "FoodDescriptions",
                "description": "#2 Bulk Import FoodDescriptions"
            }
        ],
        "start": "2015-07-14T00:00:00Z",
        "end": "2015-07-14T00:00:00Z",
        "isPaused": false,
        "hubName": "gymappdatafactory_hub",
        "pipelineMode": "Scheduled"
    }
}

我试图设置"treatEmptyAsNull"：在管道中为真，没有运气。

Answer 1

我不得不从 blob 数据集中删除"rowDelimiter": "n",。

Answer 2

大多数情况下，源文本文件具有行分隔符"\r"。使用行分隔符设置为""，最后一列的数据值"\r"不是空字符串，不会被视为空值。如果没有行分隔符设置，ADF Copy 默认情况下将使用行分隔符"\r"，最后一列将具有空字符串，并且可以被视为 null。