【SSIS】SSIS覚書 - データフローのコンポーネント(1)
前書き
久しぶりにSSISの話でもしましょう。誰も求めてないと思いますが、日本にも多分、5人ぐらいはこう言う情報を欲しがっている人がいるはずです。そのうちの1人が私です。
海外のフォーラムとかblogとかだとそれなりに詳しい情報が手に入るんですけどね…。
さて、今回はデータフロータスクでデフォルトで使用できるコンポーネントについて気が済むまで話します。と言っても、私が使ったことがあるものだけですが。（覚書だし…。）
結構量があるので、何回かにわけようかなと思っています。
知っておくべきこと
スクリプトコンポーネントの説明でしてしまったんですが、データフロータスクでは特に各フェーズの実行順序を頭の片隅に置いておく必要があります。
- 検証前フェーズ
- 検証フェーズ
- 検証後フェーズ
- 実行前フェーズ
- 実行フェーズ
- 実行後フェーズ
- クリーンアップフェーズ
これらのフェーズは各コンポーネント毎に流れていきます。データフローの組み方によっては色々な理由でデッドロックが発生することがあります。
また、DBにアクセスしに行くフェーズが各コンポーネント毎にばらばら、なんてことも結構あります。
「各コンポーネントに設定されたすべての処理を行ってから次のコンポーネントに行くとは限らない」と言うことだけは覚えておきましょう。
そしてこれも以前話してしまったんですが、コンポーネントには大まかに言って三種類あります。
- 変換元
- 変換
- 変換先
基本的には変換元から色々な変換を経て変換先に行くことになります。が、各データフローで「変換元→変換先」の流れが一つでなければならないと言うことはありません。
一つのデータフロー内に二つの入力元となるデータソースを用意し、全くばらばらなことをやっても特に問題ありません。その際SSISは自動で並列実行してくれます。
そして最後に。変換には二種類あります。
- 同期変換
- 非同期変換
ここでの同期・非同期の定義は「出力が入力と同時に発生するかどうか」です。同時ならば同期がとれていますし、同時でなければ同期もとれていません。
非同期変換とは、「すべてのデータ（レコード）が揃わないと処理が実行できない」類の変換です。例をあげるとするなら、並べ替えやマージ結合、全体結合などです。
同期変換は「一つのレコードの中のある値を見れば処理が実行できる」類の変換です。例としては派生列、条件分割、参照などでしょうか。
非同期変換は全てのデータを一旦まとめないといけないわけですから、流れてきたものを即座に変換できる同期変換よりも当然パフォーマンスは落ちます。データフロー内の非同期変換のコンポーネントの数が増えていけばいくほどそれは顕著になります。
どれぐらい遅くなるかはインプットの量にもよるんですが、洒落にならないほど大量のデータを一気に処理する場合や、何よりも速度が求められている場合はその辺も頭に入れておきましょう。
また、同期変換は入出力の同期がとれているため、データフローの上流でとある列のメタデータ（カラム名やデータ型、桁数など）を変更してもある程度自動で直してくれます。しかし、非同期変換はその同期がとれていないわけですから、上流でその辺の情報を変更するとすべての非同期変換の定義を自分で直す必要が出てきます。
そもそもSSISでパッケージを作成する段階でデータソースのメタデータが確定していない時点で激ヤバなんですが、後からの仕様変更に強くする意味でも非同期変換はなるべく使わないように設計/実装することを強くオススメします。
同期 / 非同期の詳しい説明に関してはこのドキュメントに書いてあるので、興味がある人は読んでみてください。
自分でデータフローコンポーネントを自作してみたりすると更に理解を深めることができますが、あなたが今作ろうとしているバッチ処理の方が遥かに優先度が高いので気にしなくていいです。
派生列
スクリプトコンポーネントの次に自由度が高いと言っても過言ではないコンポーネントです。大体のデータ変換はこれでできてしまいますし、いくつかの変換はこれのラッパーと言ってもいいでしょう。
派生列は式（Expression）を利用して新しい値（列）を作成することができます。組み込み関数や演算子を使うことでそれなりに自由度の高い記述が可能です。
できることを大雑把にわけると以下の通りです。
- 型のキャスト
- 列の追加
- 変数の使用
- 値の置換
既存の値の使用
派生列の変換エディターを開き、画面左上に表示される「変数とパラメーター」や「列」と言う謎のフォルダから既存の値を簡単に使用することができます。
使いたい変数 or 列を画面下の「式」と言うカラムにドラッグアンドドロップするとその項目が自動でセットされます。
既存列の置換
派生列では新規に列を作成することもできますし、既に作成されている列の値を置換することもできます。
画面下の「派生列」というカラムの中を選択すると、入力列の一覧が出てきます。ここで選択した列に対して式で新たな値をセットすることができます。
ただし入力列のメタデータは変更できません。互換性のない型に変更したり、LengthやScaleを超えるような値をセットしてしまうとエラーになります。
型に関しては（派生列に限らず）ある程度互換性があるものなら問題ありませんが、派生列の置換はあくまでも値の置換でしかありません。その後の入力列の型そのものを変えたい場合は必ず新規列を作成する必要があります。
とは言え、同じ値の列を型が違うだけで複数持つのもスマートではありませんし、最終的に変換先でマッピングする際に割と混乱します。データソースがDBならSQLのCONVERT関数を使うとか、フラットファイルなら事前に接続マネージャーレベルで型を設定しておくなど、なるべく派生列でやらないようにするのがベストプラクティスです。
新規列の追加
「派生列」カラムで<新しい列として追加>を選択すると新しい列を作成することができます。
固定値を作成したり、入力列（or 変数）をキャストしたり、関数で処理したり、まぁ基本的にExpressionで表現できることは何でもできます。
既存列の置換よりも制約が少ないのでついつい多用してしまいがちですが、先述した通り入力列が多くのカラムを持っているとかなり混乱します。上手く使い分けましょう。
OLE DB コマンド変換
身も蓋もない言い方をすればSystem.Data.OleDb namespaceのラッパーとなるコンポーネントです。と言うか、実際各プロパティに設定された値でSystem.Data.OleDbの諸々を作ってるんだと思います。
おおよそSQL実行タスクと同じような感覚で使えます。また、データフロー内でストアドプロシージャを使ったりDBに対してUPDATEを発行するようなケースではこれを使うしかないです。（スクリプトコンポーネントでもできるけど、それを言い出すとこれから説明するコンポーネントのすべてが実現できるので…。）ごめんなさい、UPDATEは各種変換先でもSQLを使うことで可能です。正確には「ストアドプロシージャを使用する」ケースではこのコンポーネントを使うほかないです。
DDLを発行することも当然可能ですが、このコンポーネントは同期変換なので一行ごとに発行されます。要するに実質使えないってことです。SQL実行タスクを使いましょう。
SQLを発行したいDBを接続マネージャーで設定し、「コンポーネントのプロパティ」タブのSqlCommandと言うプロパティにSQLを記述します。
記述するSQLは、INSERTならこんな感じです。
INSERT INTO HOGE(PIYO, FUGA) VALUES(?, ?)
SQLにプレースホルダを記述しておくことで「列マッピング」タブで入力列とパラメータのマッピングが可能です。これはUPDATEだろうがストアドプロシージャだろうが可能です。
ただし、自動で変換先列を作ってくれるのはSQL Serverに対してOLE DBコマンドを発行するときだけです。
例えばOracleなんかに同じようなことをする場合は、「入力プロパティと出力プロパティ」タブから「OLE DB コマンドの入力」ツリーを展開、「外部列」と言うフォルダを選択して「列の追加(C)」ボタンをクリックします。後はマッピングさせたい項目と同じようなメタデータを自分で設定してあげればOKです。（参考）
また、OUTPUTパラメータが設定されているストアドプロシージャに対してですが、列マッピングを行った列にOUTPUTの値が出力されます。事前に派生列などで戻り値を受け取る列を作成しておきましょう。
このSQLのプレースホルダに対してSSISの変数の値を使用したい、と言う要件は当然あると思いますが、そのままではできません。これも事前に派生列か何かで入力列を作っておく必要があります。あ、スクリプトコンポーネントだと素直にできますよ。どうです？
データ変換
派生列からキャストの部分だけ抜き出したようなコンポーネントです。
派生列で説明した通り、入力列をキャストするには新規で列を作成しなければなりませんので、置換することはできません。
これでまとめてキャストなどしようものなら目も当てられない事態に陥ることが多々あるので、やはりSQLや接続マネージャーレベルである程度事前に変換先の型に合わせておくのがベストです。
まとめ
前書きで長々としゃべりすぎて疲れてしまいました。
