title: Bulk importing JSON into SQL Server
body: <p>I need to bulk import data into a SQL Server database.</p>
  
  <p>My JSON looks like this:</p>
  
  <pre><code>$projectsJSON = @"
  [
      {
          "id":  35,
          "created_at":  "2016-01-12T11:40:36+01:00",
          "customer_id":  34,
          "name":  ".com",
          "note":  "COMXXXX-",
          "updated_at":  "2016-07-15T12:13:54+02:00",
          "archived":  false,
          "customer_name":  "PMName"
      },
      {
          "id":  23,
          "created_at":  "2010-01-11T12:58:50+01:00",
          "customer_id":  43,
          "name":  "PN",
          "note":  "{\r\n    \"Billable\": 1\r\n}\r\n",
          "updated_at":  "2017-11-24T15:49:31+01:00",
          "archived":  false,
          "customer_name":  "MSM"
      }
  ]
  "@
  
  $projects = $projectsJSON |ConvertFrom-Json
  $dt2 = New-Object system.Data.DataTable
  $dt2 = $projects|select-object id, created_at, customer_id, name, note, updated_at, archived, customer_name
  
  $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
  $bulkCopy.BulkCopyTimeout = 600
  $bulkCopy.DestinationTableName = "project"
  $bulkCopy.WriteToServer($dt2)
  </code></pre>
  
  <p>Unfortunatelly, I keep getting this error:</p>
  
  <blockquote>
    <p>Cannot convert argument "rows", with value: "System.Object[]", for
    "WriteToServer" to type "System.Data.DataRow[]": "Cannot convert the
    "@{id=35; created_at=2016-01-12T11:40:36+01:00; customer_id=34;
    name=.com; note=COMXXXX-; updated_at=2016-07-15T12:13:54+02:00;
    archived=False; customer_name=PMName}" value of type
    "Selected.System.Management.Automation.PSCustomObject" to type
    "System.Data.DataRow"." At P:\PsideSync\sync.ps1:261 char:3
    +         $bulkCopy.WriteToServer($dt2)
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodException
        + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument</p>
  </blockquote>
  
  <p>What would be the right way to import lots of JSON Data into SQL Server?</p>
  
  <p>TIA</p>