CODEWITHSUNDEEP
jsonsql-serverpowershellpsobject
Mara
Mara

Reputation: 371

Bulk importing JSON into SQL Server

I need to bulk import data into a SQL Server database.

My JSON looks like this:

$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)

Unfortunatelly, I keep getting this error:

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

What would be the right way to import lots of JSON Data into SQL Server?

TIA

Upvotes: 1

Views: 3322

Answers (1)

Palle Due
Palle Due

Reputation: 6292

As @IRon notes you are filling $dt2 twice. I assume you want to go with the second initialization.

You could get Out-DataTable from here: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

Then it's just a matter of calling

$projects = $projectsJSON |ConvertFrom-Json
$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 | Out-DataTable)

Disclaimer: I haven't tried this myself.

Upvotes: 4

Related Questions