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

Question

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

行示例:
1002,100,黄油，加盐搅打黄油，搅打加盐，Y,0,6.38,,,

最后三个假设为空。

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

Copy activity encountered a user error: ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'CarbohydratesFactor' contains an invalid value ' '. Cannot convert ' ' to type 'Decimal'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'.

食物描述AzureBlob:

{
    "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”:true，但没有成功。

Answer 1

我必须从 blob 数据集中删除 "rowDelimiter": "\n",。