SSIS で、以下のような JSON を元にテーブルを生成して、そのテーブルに対して、データをインポートするフローを作ってみました。
SSIS-Sample
テスト用でフローがいくつか入っているのですが、以下のようなフローを作成しています。
作成していた中でポイントとなりそうだった箇所をいくつかまとめておきたいと思います。
■スクリプトへの参照設定の追加
カスタムスクリプトタスクや、カスタムスクリプトコンポーネントでは、参照設定を追加することができるため、今回は、Json.NET を使用して、JSON の読み込みを行っています。
public void Main()
{
string targetfile = Dts.Variables["User::TargetFile"].Value.ToString();
string sql = string.Empty;
ConnectionManager cm = Dts.Connections["sqlconnection"];
System.Data.SqlClient.SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
sqlCon = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlCmd.Connection = sqlCon;
StreamReader sr = new StreamReader(targetfile, Encoding.GetEncoding("Shift-JIS"));
string text = sr.ReadToEnd();
List<JsonObject> array = JsonConvert.DeserializeObject<List<JsonObject>>(text);
array.Sort((a, b) => a.order - b.order);
string T1 = string.Empty;
T1 = String.Format("DROP TABLE IF EXISTS [{0}];CREATE TABLE [{0}](", Path.GetFileNameWithoutExtension(targetfile));
foreach (var o in array)
{
T1 += "\n" + o.name + " " + o.type + ",";
}
T1 = T1.Substring(0, T1.Length - 1);
T1 += "\n)";
sqlCmd.CommandText = T1;
sqlCmd.ExecuteNonQuery();
cm.ReleaseConnection(sqlCon);
Dts.Variables["User::ImportTable"].Value = Path.GetFileNameWithoutExtension(targetfile);
Dts.Variables["User::ImportFilePath"].Value = Path.GetDirectoryName(targetfile);
Dts.Log(targetfile, 0, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
public class JsonObject
{
public int order { get; set; }
public string name { get; set; }
public string type { get; set; }
}
今回は「Newtonsoft.Json.dll」を追加しているのですが、アセンブリを追加する際は、DLL は、「C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn」か「C:\Program Files\Microsoft SQL Server\130\DTS\Binn」のいずれかに配置する必要があります。
x86 / x64 のどちらに配置するかは、dtsx をどちらで動作させるかに依存します。
キーの設定などはまだ実装していないのですが、以下のような JSON を読ませることで、ファイル名のテーブルを作成することができます。
[
{
"order": 1,
"name": "C1",
"type": "int"
},
{
"order": 99,
"name": "C99",
"type": "nvarchar(50)"
},
{
"order": 2,
"name": "C2",
"type": "int"
}
]
■カスタムスクリプトタスクのブレークポイントを設定
スクリプトコンポーネントはブレークポイントを設定しても効かないようですが、カスタムスクリプトタスクについてはブレークポイントを動作させることが可能です。
Why the breakpoints that I set in my “Script Task” (not “Script Component” in the Data Flow *) never hits ? に記載されていますが、ブレークポイントを動作させるためには、上述の画像の「Run64BitRuntime = False」に設定する必要があるようです。
64 ビットだとブレークポイントが動作しないかと。
■動的にフラットファイル接続先の対象ファイルを変更する
接続マネージャーの接続先については「ConnectionString」のパラメーターを変更することで、外部から設定を変えることができます。
今回は、特定のディレクトリ配下のファイルを「一括挿入タスク」を使用して、取り込みを行っています。
処理対象のファイルについては「Foreach ループコンテナー」を使用して判断しているのですが、このループ内で取得したファイル名を動的にフラットファイルの接続マネージャーに埋め込みたいというのが目的です。
「ConnectionString」に変数が割り当てられれば良かったのですが、パラメーターのみのようなので、一括挿入タスクを実行する前にカスタムスクリプトタスクを配置してみました。
このスクリプトでは、Foreach で対象となったファイルを、一括挿入タスクで使用している、接続マネージャーの「ConnectionString」のパラメーターに設定する処理を行っています。
Dts.Connections["BulkImport"].ConnectionString = Dts.Variables["User::ImportFile"].Value.ToString();
これで、Foreach 内の処理対象となっているファイルが、一括挿入タスクの接続文字列として設定されますので、接続マネージャーの処理対象のファイルを切り替えながら、Bulk Insert を実施することができます。
追記
こんなことしないでも、一括挿入タスクのプロパティウィンドウの Expression で、 ConnectionString を変更すればよいだけですね。。。。
https://msdn.microsoft.com/ja-jp/library/ms166797.aspx
久しぶりに SSIS 触っているのですが、カスタムスクリプト使うと何でもあり感がすごいですね。