Road to DBD

SSISで一時テーブルを使って処理を行いたいときのポイント
「SQL実行タスク」を実行してワークテーブルを作成し、「データフロータスク」でそのワークテーブルを使うケースを考えてみる。

1)接続を維持する
・SQL ServerのOLEDB接続マネージャがデフォルトのままだと、通常は接続はタスク開始時に確立され、終了時に切れる。したがって、「SQL実行タスク」が完了すると接続が切れる。
・ローカル一時テーブルはセッション単位に有効であるため、作成してもタスク終了時点でなくなってしまう。グローバル一時テーブルはセッションをまたがって参照できるが、参照しているセッションがゼロになった瞬間になくなってしまうため、やはりタスク終了時点でなくなってしまう。
・したがって、SQL ServerのOLEDB接続マネージャのRetainSameConnection=Trueに変更する。これにより一度確立したセッションはパッケージ終了まで維持される。

2)データフロータスクで一時テーブルを使う
・データフロータスクの変換先としてテーブルを使うには、設定時にテーブルが存在していないと選べないため、通常はSSMS(SQL Server Management Studio)などを使って、グローバル一時テーブルを作成し、変換先のリストに登場させる。（設定完了後は、SSMSの接続を切るなどして、グローバル一時テーブルはDROPしてしまうが、それ以降は「OLEDB変換先」はエラーマークが表示された状態になる）
・このまま実行すると、実行前の検証で「変換先のテーブルが存在しない」というエラーが発生する。実際にタスクを実行する瞬間には、一時テーブルが存在することになるため、実行前タスクの内容を検証しないようにする。具体的には「データフロータスク」のDelayValidation=Falseに変更する

3)データフロータスクでローカル一時テーブルを使う
・ローカル一時テーブルも使うことはできる。しかし、2)の方法では同じセッション一時テーブルを作成することはできないため、2)の設定のあと「OLEDB変換先」のプロパティ(OpenRowSet)を書きかえる。

4)一時テーブル作成は並行実行させない
・上記フローを並列して複数実行するように設定すると、うまくいかないことがある。たとえば、一時テーブルを作成する「SQL実行タスク」と、その一時テーブルにデータを取り込む後続の「データフロータスク」をワンセットとして、３つ並べてシーケンスコンテナに入れて実行した場合、どれか１つがエラーになる可能性が高い。
・エラーになるとき、SQLプロファイラで監視してみると、以下のような処理が行われる

Event Class         TextData                                SPID

Audit Login         -- network protocol: TCP/IP             53

SQL BatchCompleted  CREATE TABLE #TEMP2 (F1 char(2),F2 int) 53

Audit Login         -- network protocol: TCP/IP             54

SQL BatchCompleted  CREATE TABLE #TEMP3 (F1 char(2),F2 int) 53

SQL BatchCompleted  CREATE TABLE #TEMP1 (F1 char(2),F2 int) 54

Audit Logout                                                54

（以後SPID=53にてデータ取り込み処理）

　３つの一時テーブルを作成するときに、当初からあるセッション(53)に加えて、もう一つのセッション(54)が新たに起動し、#TEMP1を作成してから切れている。このような動きになる場合は、セッション(54)が切れた瞬間に#TEMP1はなくなり、#TEMP1に対する後続のデータフロータスクがエラーとなる。

・並列実行の判断はSSISが行うため、上記を回避するためには、
　a)一時テーブルを作成するSQL実行タスクは並列実行させない
　b)それぞれのフローに違うOLE接続マネージャを割り当てる
　c)パッケージのMaxConcurrentExecutablesを-1から1に変更する(-1はCPU数+2を示す既定値）
　d)すべての処理を直列に配置する