SQL Server 2016 では、 JSON 関数が追加されていて、SSIS でもサクッと JSON のデータをロードすることができるようになっていますので、軽くメモを。
どの程度のデータまで対応できるかは試していないのですが、今回は以下のような JSON を用意しています。
[
{
"Col1": 10,
"Col2": 11,
"Reserve1": 999
},
{
"Col1": 20,
"Col2": 22,
"Reserve2": 888
}
]
Col1/2 は必須の項目、Reserve1/2 は任意項目のようなレイアウトを想定しています。
現状の SSIS はデフォルトのコンポーネントでは、JSON をロードするようなコンポーネントはないかと思いますので、カスタムスクリプトと変数を使ってまずは、さくっと変数に JSON をロードしてしまいます。
JSON を格納する変数を、ReadWriteVariables に設定し、以下のようなスクリプトを記載します。
using System.IO;
namespace ST_dd009bdbbdc747729089305463f2036c
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
Dts.Variables["User::JSON"].Value = File.ReadAllText(@"C:\Import\TEST.json");
Dts.TaskResult = (int)ScriptResults.Success;
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
これで、変数に JSON の内容が格納されますので、あとは、後続のスクリプトタスクでこの変数を利用したクエリを記載します。
今回は以下のようなクエリを記載しています。
DECLARE @json nvarchar(max) = ? SELECT * INTO T1 FROM OPENJSON(@json) WITH (Col1 int 'strict $.Col1', Col2 int 'strict $.Col2', Reserve1 int '$.Reserve1', Reserve2 int '$.Reserve2')
パラメーターには、先ほど変数に格納した JSON を渡しています。
これを実行すると、スクリプトタスクに設定している接続先に以下のような形で JSON の内容が展開されたテーブルが作成されます。
今回は、SELECT INTO でテーブルの作成と INSERT を同時に行っていますが、これは INSERT INTO SELECT だったり、カーソルのベースとしても利用できますので、色々な利用方法があるのではないでしょうか。