title: Azure Data Factory - Copy Activity empty value doesn't change to null error
body: <p>I have an azure blob with txt file. Some columns have empty values, so when they are saved into database table, they are NULL. I can make it work with direct SQL and SSIS ETL packages.</p>
  
  <p><strong><em>Example of row:</em></strong><br>
  1002,100,Butter,whipped with salt BUTTER,WHIPPED W SALT,Y,0,6.38,,,</p>
  
  <p>Last three are suppose to be null.</p>
  
  <p>When I try with ADF I get this error:</p>
  
  <blockquote>
    <p>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,'.</p>
  </blockquote>
  
  <p><strong>FoodDescriptionsAzureBlob:</strong></p>
  
  <pre><code>{
      "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": {}
      }
  }
  </code></pre>
  
  <p><strong>FoodDescriptionsSQLAzure:</strong></p>
  
  <pre><code>{
      "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
          }
      }
  }
  </code></pre>
  
  <p><strong>Pipeline:</strong></p>
  
  <pre><code>{
      "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"
      }
  }
  </code></pre>
  
  <p>I tried to set up "treatEmptyAsNull": true in pipeline, with no luck.</p>