Bulk importing JSON into SQL Server

Question

我需要将数据导入到SQL Server数据库中。

我的JSON看起来像这样：

$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":  "{rn    "Billable": 1rn}rn",
        "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)

不幸的是，我一直遇到此错误：

无法转换参数"行"，带有值：" System.Object []"，用于 " writeToserver"键入" system.data.datarow []："无法转换 "@{id = 35; create_at = 2016-01-12t11：40：36 01：00; customer_id = 34; 名称= .com;注意= comxxxx-;UPDATED_AT = 2016-07-15T12：13：54 02：00; 存档= false;customer_name = pmname}"类型的值 " selected.system.management.automation.pscustomobject" " system.data.datarow"。"在P： psidesync sync.ps1：261 char：3 $ bulkcopy.writetoserver（$ dt2） ~~~~~~~~~~~~~~~~~~~~~~~~~~ categoryInfo：未指定：（:) []，methodexception 完全QualifiedErrid：MethodArgumentConversionInvalidCastArgument

将大量JSON数据导入SQL Server的正确方法是什么？

tia

Answer 1

作为@iron Notes，您两次填充$ DT2。我认为您想使用第二个初始化。

您可以从这里获得以下内容：https：//gallery.technet.microsoft.com/scriptcenter/4208A159-A52E-4B99-4B99-83D4-80484848468D29DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD

那只是打电话的问题

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

免责声明：我自己没有尝试过。