title: Data Factory copy csv to SQL cannot convert empty data
body: <p>Encountered below various errors caused by empty data when building a very basic Copy Data task from File Sharing to Azure SQL:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>And here is another one I believe caused by the same reason:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>All I need is to treat empty data as NULL when copying to SQL Tables. The only option I have found is &quot;Null value&quot; in my CSV dataset; and it is set to nothing as default.</p>
  <p>Below is the code of CSV dataset:</p>
  <pre><code>{
      &quot;name&quot;: &quot;CSV&quot;,
      &quot;properties&quot;: {
          &quot;linkedServiceName&quot;: {
              &quot;referenceName&quot;: &quot;CSV&quot;,
              &quot;type&quot;: &quot;LinkedServiceReference&quot;
          },
          &quot;parameters&quot;: {
              &quot;FileName&quot;: {
                  &quot;type&quot;: &quot;string&quot;
              }
          },
          &quot;annotations&quot;: [],
          &quot;type&quot;: &quot;DelimitedText&quot;,
          &quot;typeProperties&quot;: {
              &quot;location&quot;: {
                  &quot;type&quot;: &quot;AzureFileStorageLocation&quot;,
                  &quot;fileName&quot;: {
                      &quot;value&quot;: &quot;@dataset().FileName&quot;,
                      &quot;type&quot;: &quot;Expression&quot;
                  },
                  &quot;folderPath&quot;: &quot;output&quot;
              },
              &quot;columnDelimiter&quot;: &quot;,&quot;,
              &quot;escapeChar&quot;: &quot;\\&quot;,
              &quot;firstRowAsHeader&quot;: true,
              &quot;quoteChar&quot;: &quot;\&quot;&quot;
          },
          &quot;schema&quot;: []
      }
  }
  </code></pre>
  <p>The csv file does use double quotation marks as the qualifier. And those empty data in source files look like this:</p>
  <pre><code>&quot;b139fe4d-f48a-4158-8196-a43500b3bf02&quot;,&quot;19601&quot;,&quot;Bar&quot;,&quot;2015/02/02&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;
  </code></pre>