【SSIS】SSIS覚書-SQL実行タスク

前書き

SSIS ツールボックスから選べるタスクの中にSQL実行タスクがあります。

INSERT/UPDATE/DELETEは勿論、SELECTで値を取ってきて変数に入れるなんてことも出来ますし、（ETLツールでそれをやるべきかどうかはおいといて）やろうと思えばDDLもいけます。何でもありです。T-SQLが書けるのでちょっとしたロジックを仕込むことも可能ですし、ストアドプロシージャを呼び出すことも可能です。

とは言え、あまり複雑なことをするつもりであればそもそもデータフロータスクを作ればいいのであって、超単純なINSERT/UPDATEかSELECT/DELETEの用途で使われることが多いと思います。

特に「DBから値を取ってきて変数に格納する」場合は（スクリプトを書かない限り）この方法しかないので、案外良く使うタスクではあります。

SQLの実行結果を変数に格納する-共通事項

SQL実行タスクエディタを開き、結果セットのResultSetを「なし」以外の何かにします。先に言っておくと、XMLはやったことがないので説明しません。って言うかこの機能マジで誰が使うんですかね？

選択後、左ペインの「結果セット」から「追加(A)」を押し、「結果名」と「変数名」を指定します。変数名はともかく結果名はどうでも良さそうな感じがしますが、使用するDBのクライアントによっては上から0始まりでシーケンシャルな番号を振ったほうがいいかもです。

SQLの実行結果を変数に格納する-単一行

単一行の場合であればこれだけでOKです。SELECTしてくる項目が複数ある場合は項目数分だけの結果名と変数名を指定してあげれば指定した順番でちゃんと取ってきてくれます。

ただ、当然ながらSSISの変数の型とDBの型で互換性がないと落ちます。型チェックなんてしてくれないので間違えないよう注意しましょう。

SQLの実行結果を変数に格納する-完全な結果セット

完全な結果セットの場合はObject型の変数を一個指定しておけばOKです。ドキュメントを読めばわかりますが、なんとこれ、実体はADODB.Recordsetです。いくらなんでももうちょっとなんかあったろ…。

そんなわけで完全な結果セットを使いたい場合はスクリプトを書かないと中のデータを取得できません。ADODBを参照設定に追加して変数をキャストしましょう。流石にそのまま使うのはしんどいので、PreExecute内で一旦RecordsetをDataSetに変換してしまった方がいいです。


public override void PreExecute()
{
    base.PreExecute();
    
    var rs = this.Variables.rsHoge as ADODB.Recordset;
    if (rs != null)
    {
        using (var da = new OleDbDataAdapter())
        using (var ds = new DataSet())
        {
            da.Fill(ds, rs, string.Empty);
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow data in ds.Tables[0].Rows)
                {
                    // TODO:何らかのメンバ変数にデータを入れる
                }
            }
        }
    }
}

SQLの実行結果を変数に格納する-0件データのハンドリング

一番面倒なのは、結果セットを受け取るよう設定すると、SQLの結果が0件の時にエラーになってしまうことです。

0件の可能性がある時は優先順位制約の実行結果を「完了」もしくは「失敗」にしましょう。（タスクから出てる矢印を右クリックするだけでも設定可能）

0件ならそれはそれで別にいいなら「完了」で十分ですし、0件時に何らかの他の値で初期化したいのであれば「失敗」にしましょう。ただし、当前0件じゃないエラーもハンドリングしてしまいます。これはどうにもなりません。ファックすぎて言葉もありません。

使用するSQLに変数の値を適用する

以下のどちらかで実現可能です。

SQLにプレースホルダを埋め込む
ExpressionsでSqlStatementSourceに値を設定する

流石に「SELECTで取得する項目を動的に切り替える」となると後者しかありませんが、WHEREやINSERT / UPDATEの値を実行時に変える、程度なら前者の方がわかりやすいです。

逆に、ストアドプロシージャのOutputとなる変数をSSISの変数に格納する、なんてことをしなきゃいけない場合は前者じゃないと無理です。

ちなみに保守性と言う意味ではどっちも同じぐらい面倒です。

後者はまぁなんとなくわかると思うので、前者だけ説明します。まずは適当にSQLを準備します。

UPDATE FUGA
SET
    HOGE = ?
WHERE
    PIYO = ?

次に、左側のペインから「パラメーターマッピング」を選択します。これも結果セットと同様、追加ボタンから追加していきます。

InputなのかOutputなのかだとか、データ型をちゃんと設定してあげましょう。今回の例のように単純なDMLであれば全部Inputでいいです。

また、プレースホルダの順番と、ここで指定した変数の順番は一致してないといけません。なのに何故かパラメーターマッピングの順番を後から変えることは出来ません。いい加減にして欲しいですね。

TRUNCATEを行う際の注意点

よくありそうな要件として「何らかのワークテーブルをTRUNCATEする→データフローでそのワークテーブルにデータをINSERTする」って言うのが考えられます。

当然DDLが書けるんだからTRUNCATEだって出来ます。DROP / CREATEもやろうと思えばいけるんじゃないでしょうか。

が、何にも考えずにこれをやるとデッドロックが発生することがあります。SSISのトランザクション制御が原因です。

パッケージのTransactionOptionをRequiredにしている場合のケースはKnown Issueとして紹介されていますが、デフォルトのSupportedでも再現する可能性があります…と言うか、したことがあります。

大分前の話なので再現手順がちょっとうろ覚えなんですが、確か直前にSQL実行タスクでTRUNCATEしたワークテーブルを直後のデータフロータスクでOLE DBコマンドコンポーネント経由でUPDATEしようとした時にデッドロックになりました。

まぁ何にせよロックの取り合いが原因でしょう。SQL ServerではTRUNCATE TABLEでも普通にロールバックできるなんてことを知ってる人は案外少ないと思うので、TRUNCATEを行うSQL実行タスクのTransactonOptionを「NotSupported」にしてしまい、何が何でも即コミットしてもらうようにした方がいいかもしれません。

まとめ

SSISではいつものことですが、「何でこれができねーの？」みたいなところがちらほらあります。

嫌になったらどんどんスクリプト書きましょう！何にも考えずに！そっちの方がストレス減らせるし！