【SSIS】SSIS覚書 - データフローのコンポーネント(3)

SSIS

前書き

前々回、前回の続きです。

一旦ここで使ったことがあるものは全部説明しきってしまいます。

知っておくべきこと

データフローは各コンポーネントへのパスごとにメタデータと呼ばれるものを所持しています。これは以下の情報から構成されています。（矢印みたいなやつをダブルクリックすると見れる）

  • 名前
  • データ型
  • 有効桁数
  • 小数点以下桁数
  • 長さ
  • コードページ
  • 並べ替えキーの位置
  • 比較フラグ
  • 基になるコンポーネント

このメタデータは直接いじることができません。「基になるコンポーネント」の処理によって決まります。

データフローの検証フェーズでは「本当にこのメタデータ通りにデータが流れるかどうか」を検証します。デザイン時と実行時のメタデータに一つでも不一致となるものがあると、エラーとしてその後のデータフローの処理を実行しません。

このメタデータに関して注意しなくてはならない点が二点あります。

まず一つは、非同期変換についてです。これは前々回に説明しましたが、入力と出力が同期されていないために、上流でメタデータが変更されても自動で修正されません。

しかし、これは事前にエラーとして検出されるので、精々「非同期変換を使いすぎると面倒なことになる」程度の問題です。

厄介なのはもう一つの注意点です。SSIS以外の外的要因によって実行時のメタデータが変わってしまうことがあります。

具体例としては、「開発環境と本番環境でデータソースのある項目の桁が違う」なんてケース。開発環境はnvarchar(10)で、何故か本番環境ではnvarchar(9)だったりとか。それもエラーとなります。メタデータの「長さ」がデザイン時と実行時で一致していないからです。

「桁が小さくなっただけだから今まで通り読み込めるだろう」と思ったら大間違いです。使用する項目のメタデータが変更された場合は必ずパッケージも修正する必要があります。楽はできないのです。面倒ですね。

文字マップ変換

ひらがなをカタカナにするとか、半角を全角にするとか、そう言ったことができます。

「変換先」を「新しい列」にすると新規列の作成、「埋め込み先変更」にすると対象項目の値を置換します。当然1バイトのものをマルチバイトに置換する場合、桁あふれが発生する可能性が出てきます。

私は主に半角→全角変換のためにこれをよく使うんですが、半角カタカナや記号、数字なんかも割と柔軟に全角にしてくれるのでありがたいです。

やれることの内容からしても、恐らく内部でStrConvメソッドを呼んでいるだけだと思われます。

集計変換

入力行に対してGROUP BYをかけます。当然非同期変換です。

主にデータソースがDB以外のものをGROUP BYしたいときに使います。HAVINGはできませんが。

「キーの数やカウントの数が事前にわかってるならそれを設定しておくとパフォーマンスがあがるよ！」と書かれていますが、そんな状況は割とレアケースな気がします。

全体結合変換

変換ではないです。複数の入力を1つにまとめることができます。条件分岐や参照などで枝分かれしてしまったデータを本流に戻すとか、そんなときによく使います。

当然のことながら非同期変換ですので、あまり多用すべきではありません。チェック項目がありすぎて何度も出力を分岐→結合しなくてはならないならスクリプトコンポーネントでチェック処理をひとまとめにしてしまいましょう。

と言うのも、全体結合は上流のメタデータ変更に滅法弱いコンポーネントの一つだからです。仮に入力元データソースのメタデータが変更された場合、すべての全体結合を修正する必要があります。しかもその修正方法は「メタデータが変わった項目を一旦削除してもう一度設定し直す」と言うひどいものです。

ちょっとしたパッケージならともかく、全体結合への入力が5つぐらいあるとか、それぞれの入力で使用する項目が微妙に違うとか、そんなケースだとデグレが発生してもおかしくありません。

一つのデータフロー内に「出力分岐→同期変換→全体結合」のパターンが頻出するようであればそれは間違いなくバッドスメルです。標準コンポーネントだけで何もかもすませようとする人が作ったパッケージにこの傾向が多く見られます。そう言ったものを修正することになると本当に純粋な殺意が芽生えるのでやめてください。本当にやめてください。家に帰してください…。

マージ結合変換

これも変換ではないです。全体結合がUNIONなら、マージ結合はJOINのイメージです。そしてこれも非同期変換です。

複数の入力のキー同士を比較して結合することができます。キー項目は事前に並べ替えしておく必要があります。

また、同時に受け付ける入力は二つだけです。三つ以上の入力をマージ結合したい場合はそれだけマージ結合の数自体を増やす必要があります。

結合の種類として以下の三つから選択することができます。

  • 内部結合（INNER JOIN）
  • 左外部結合（LEFT OUTER JOIN）
  • 完全外部結合（FULL OUTER JOIN）

片方の入力でキーが重複していると結合後のレコードが増えてしまうとか、そう言うところも含めて本当にSQLのJOINと同じようなものです。逆に言えば、データソースが両方とも同じDBなら普通にJOINで取ってきた方が100倍楽です。

割とどうでもいいんですが、変換エディターを開くと一番最初に結合の種類のリストボックスにフォーカスがあり、マウスホイールをころころ動かしていると結合の種類がいつの間にか変わっていると言うことがあります。一回それでやらかしたことがあります。皆さんは気をつけましょう。

また、これと同じような処理をスクリプトコンポーネントで実装したことがあるんですが、性能が全然出なくてほとんどフリーズ状態になりました。渋々マージ結合に切り替えたら数ミリ秒で終わりました。ソート済み、と言うのがでかいんでしょうね。

最後に、上流のメタデータが変更されるとコンポーネント自体を作成し直す必要があります。マージ結合が必要ならばなるべく早い段階（データフローの上流）で結合してしまうことをオススメします。

OLE DB ソース

OLE DB 接続マネージャーを使用してデータソースを作成します。

実際にデータソースを作成する方法としては以下の四つがあります。

  • テーブルまたはビューを直接指定
  • テーブルまたはビューの名称が入っている変数を指定
  • SQL
  • SQLが入っている変数を指定

テーブルやビューを直接指定する方法はまったくもってオススメしません。SELECT *でとってくるからです。

SQLはまぁいいでしょう。普通にSQLを書くだけです。SQL Server 2012以降であれば、WITH RESULT SETSオプションを使用することでテーブル型を返すストアドファンクションも使用可能です。

最後にSQLが入っている変数を指定する方法ですが、若干面倒です。以下のルールは必ず守る必要があります。

  • 指定する変数には事前にある程度完成しているSQLを記述する（入力のメタデータがないとその後のデータフローをデザインできない）
  • デザイン時のメタデータと実行時のメタデータはかならず一致させる
  • 検証フェーズまでには変数の中身を確定させる

と言うわけで中々条件がシビアです。が、ある程度はSQLで逃げることができます。

例えばこんな二つのテーブルがあったとします。

  • MST_FUGA
    • FUGA_1 (nvarchar(30))
    • FUGA_2 (decimal(5, 0))

ある条件によって、MST_HOGEをとってくるか、MST_FUGAをとってくるかを分岐させたいとしましょう。その場合、変数にはこんなSQLを入れるようにします。

-- MST_HOGEをとってくるSQL
SELECT
    CONVERT(nvarchar(30), HOGE_1) no_1
    , HOGE_2 no_2
    , HOGE_3 no_3
FROM
    MST_HOGE


--  MST_FUGAをとってくるSQL
SELECT
    FUGA_1 no_1
    , CONVERT(int, FUGA_2) no_2
    , NULL no_3
FROM
    MST_FUGA

このようにすることで、デザイン時と検証フェーズでのメタデータの不整合を防ぐことができます。

勿論データフローでの各種変換は色々と面倒になります。それを踏まえた上でメリットの方が上回っているか考えたほうがいいでしょう。

また、SQLにプレースホルダを設定しておくと変数値を使うことができます。やり方はSQL実行タスクと大体一緒です。エディターの「接続マネージャー」ペイン右側にある「パラメーター(P)」と言うボタンをクリックすると設定できます。

ちなみに、エディタから書けるSQLの文字数には限界があります。（確か4000字ぐらいだったかな？）

洒落にならない量のSQLを一気に書く場合は一旦SQLファイル（*.sql）に保存し、「参照(W)」から読み込めばOKです。

ODBC 変換元

OLE DB ソースと大体一緒ですが、何故か変数が一切使えません。

そもそもSSISのODBCはバグだらけのゴミなので一切使わないでください。

フラット ファイル ソース

大して特筆することはないんですが「データソースのNULL値をデータフローでNULL値として保持する(R)」と言うところにチェックがないと空の文字列として扱います。

メタデータにも特に影響はないし、一応つけておいたほうがわかりやすいんじゃないかなと思います。

読み込むファイルを動的に変更したい場合は接続マネージャー側のConnectionStringを動的に変更すればOKです。

OLE DB 変換先

OLE DB コマンドと大体一緒です。

テーブル or ビューを指定するか、SQLを書くことでデータを登録できます。テーブル or ビューを指定する場合はINSERTのみです。

UPDATEをする場合はSQLにプレースホルダを記述し、マッピングすればOKです。DELETEやMERGEもできます。ストアドプロシージャはちょっと無理っぽいです。（参考）

フラット ファイル変換先

以前に書いた[SSIS]ヘッダ付きのCSVにデータを追記すると言う記事の内容以外に特筆することはありません。

フラット ファイル ソースと同様、書き出すファイルを動的に変更する場合は接続マネージャーのプロパティをいじります。

まとめ

と言うわけで、スクリプトコンポーネント以外の今までに使ったことがあるコンポーネントのノウハウをそれなりに説明してみました。

誰かの役に立てば幸いです。