【SSIS】SSIS覚書-スクリプト関連(2)

記事のフォーマットを他とあわせるついでに全体的に加筆・修正しました。

[2014/06/30追記ここまで]

SSIS覚書シリーズ一覧

前書き

前回はスクリプトタスクの話をしたので、今回はデータフローで使えるスクリプトコンポーネントの使い方についていくつか解説していきます。

知っておくべきこと

データフローの話になってしまいますが、コンポーネントには「変換元」「変換」「変換先」の三種類があります。

「変換元」のコンポーネント（データソース）から最終的に「変換先」のコンポーネント（データソース）に到達するのがデータフローの大原則です。これを破ることはできません。

スクリプトコンポーネントはこの三つのうちのどれにでもなることができます。が、まぁ、ほとんどは変換として使うと思いますし、この記事でも変換を前提として解説していきます。

また、これもデータフローの話になりますが、SSISでは各タスクに色々なフェーズが存在します。データフローはおおよそ以下の流れで進んでいきます。

検証前フェーズ
検証フェーズ
検証後フェーズ
実行前フェーズ
実行フェーズ
実行後フェーズ
クリーンアップフェーズ

注意しなくてはならないのは、各フェーズごとに全てのコンポーネントを舐める、と言うことです。

例えば「フラットファイル変換元 → 派生列 → 参照 → フラットファイル変換先」と言うデータフローがあったとしたら、検証前フェーズで「フラットファイル変換元 → … → フラットファイル変換先」、検証フェーズで「フラットファイル変換元 → … → フラットファイル変換先」と見ていきます。

なので、SSDT上での見た目とは違い、思わぬところでデッドロックが発生したり、矢印の順番通りに進んでいないように見えることが普通にありえます。

スクリプトコンポーネントも例外ではありません。特に変数のデッドロックはこの仕組みを知らないと簡単にハマるので注意しましょう。

各データの設定

入力列

変換として使用する場合、普通にパス（矢印のやつ）をつなげてあげれば、それまでに変換してきたデータを使うことができます。

「入力列」ペインで使用したい項目にチェックを入れましょう。「使用法の種類」に「ReadOnly」と「ReadWrite」がありますが、まぁ読んで字の如くです。

出力列

新しい列を出力したい場合は以下の方法で可能です。

「入力および出力」ペインを選択
出力 0のツリーを展開
出力列（フォルダのアイコン）をクリック
列の追加ボタンをクリック

適当なDataType、Length、Nameを設定してやることでスクリプト中から呼び出せるようになりますが、一括設定の類のものはないので、大量に出力列を作成する必要があるとかなりしんどいです。

変数

設定に関してはスクリプトタスクと一緒です。

「スクリプト」ペインのReadOnlyVariables / ReadWriteVariablesに設定してやればOKです。ただし使い方はスクリプトタスクとは異なるので注意しましょう。（後述）

実際のコード

実際にスクリプトを生成すると自動的に以下の三つのメソッドが宣言されているはずです。

public override void PreExecute()
public override void PostExecute()
public override void 入力0_ProcessInputRow(入力0Buffer Row)

この辺はカスタムオブジェクトでデータフローコンポーネントを自作していると「ああー…」ってなるんですが、そんなことがする人がこんな記事を読んでいるわけがないのでちゃんと説明しましょう。

PreExecuteとPostExecuteは先述したデータフローのフェーズに関係してきます。それぞれ「実行前フェーズ」と「実行後フェーズ」に対応しています。

入力0_ProcessInputRowとか言うひどい名前のメソッドは「実行フェーズ」に該当します。これのシグネチャであるRowに、先ほど設定した入力列と出力列のプロパティが自動で設定されています。

これ（入力0Buffer）の実態、実はソリューションエクスプローラーで見れる「BufferWrapper.cs」にいます。まぁ見る必要は全くありませんが。

注意点1：とりあえずdo while(Row.NextRow())でループさせよう

本来スクリプトコンポーネントではそのままコードを書けば同期変換の処理として扱ってくれるはずなんですが、何故かたまにうまくいかないことがあります。

具体的にどういう条件で発生しているのかわからないんですけど、最終的に出力されるデータを見るとスクリプトが適用されている行があったりなかったりします。でも特に何もしなくても全行に適用されるものもある。本当に謎。

とは言え、適用されない行が存在するのは普通に考えてアウトなので、非同期変換の方を使えばとりあえず確実に動きます。（非同期/同期変換の違いはまた今度）

そんなわけでとりあえずスクリプトコンポーネントを作ったらこのスニペットをぶち込みましょう。

public override void 入力0_ProcessInputRow(入力0Buffer Row)
{
    do
    {
        //やりたいこと
    } while (Row.NextRow());
}

msdnのコード例では当たり前のようにwhile構文で書かれてるけど、NextRow()したら最初の一行は無視されるのでdo while構文にしましょう。（常識だと思うんだけど…。）

ただ、当然空データで飛んできているのにNULLチェックを怠っているとエラーは起きます。空データはこないとわかっている/入力列がNot Nullの場合でも、一応しておいたほうがいいです。~~どうせそんな仕様はころころ変わるし…。~~

一応「Row.EndOfRowset()」と言うメソッドも用意されているんですが、スクリプトコンポーネントにおいてこのメソッドはfalse以外を返さないので一切触れないようにしましょう。（どこかの記事で見たんだけどURLを失念してしまった。適当に自分でテストしてみるとわかる。）

注意点2：NULLチェックをしよう

スクリプトコンポーネントでのNULLチェックは若干面倒くさいです。

これはダメな例。

public override void 入力0_ProcessInputRow(入力0Buffer Row)
{
    do
    {
        if (Row.hoge != null) Row.hogehoge = "piyo";
    } while (Row.NextRow());
}

自動で生成されるIsNullプロパティを見てやる必要があります。

public override void 入力0_ProcessInputRow(入力0Buffer Row)
{
    do
    {
        if (Row.hoge_IsNull) Row.hogehoge = "piyo";
    } while (Row.NextRow());
}

逆にNULLを設定する方法ですが、基本的にないと思ったほうがいいです。

どうしてもNULLを設定したい場合は出力列を一つ増やし、何の値も入れなければ勝手にNULLになります。

ちなみにリフレクションを使ってあんなことやこんなこともできます。覚えておいて損はないです。

注意点3：変数を読み取るときはPreExecute、変数に書き込む時はPostExecuteでやること

スクリプトコンポーネントから変数を呼び出すには「this.Variables.変数名」としてやればOKです。インテリセンスが利いたり、型が自動で設定されてたりと、スクリプトタスクでもやってほしいものです。

ただし、このようなコードを書くと「読み取りおよび書き込みアクセス用にロックされた変数のコレクションは、PostExecute の外側では使用できません。」という例外が発生します。

public override void 入力0_ProcessInputRow(入力0Buffer Row)
{
    var i = 0;
    do
    {
        i++;
    } while (Row.NextRow());

    this.Variables.RowCount = i;
}

抜け道は存在しないので、とりあえず素直にPostExecuteで書き込むよう修正してあげましょう。

private int _rowCount;

public override void PostExecute()
{
    base.PostExecute();
    this.Variables.RowCount = _rowCount;
}

public override void 入力0_ProcessInputRow(入力0Buffer Row)
{
    do
    {
        _rowCount++;
    } while (Row.NextRow());
}

このコードは「流れてきたデータの件数をカウントし、その総数を変数に格納する。」ってものなんですが、実はこれ、行数変換と全く同じことをしています。

どう考えてもこれだけじゃ意味がないので、「件数がn件以上ならAへ、それ未満ならBの出力へ」と言う要件があったとします。分岐はとりあえず条件分割あたりを使って適当に式を書きます。

実際にやってみるとわかると思いますが、何件あろうとAの出力には絶対に行きません。厳密には変数RowCountの初期値がn以上なら全部Aに行き、n未満なら全部Bに行きます。つまり、PostExecuteで書き換えられた変数の値はそのデータフロー内での条件には使うことができないと言うことです。（これは行数変換でカウントしても同じ結果になる。）

これもやはりデータフローのフェーズの影響です。

条件分割の判断は「実行時フェーズ」で行われます。が、判断するための変数の値は「実行後フェーズ」で書き込まれるためです。

スクリプトコンポーネントで設定した変数の値は別のタスクでのみ使用できると考えましょう。これはもうどうしようもありません。

また、読み取り専用の変数であってもデッドロックが発生することがあります。

例えばあるスクリプトコンポーネントで読み取り専用の変数を入力0_ProcessInputRowで呼び出すとします。ここまではまぁ、一応大丈夫です。

しかし、その変数が別のスクリプトコンポーネントのReadWriteVariableに指定されていると実行後フェーズでデッドロックに陥りフリーズします。

どうせ読み取り専用なのでPreExecute(実行前フェーズ)でメンバ変数に受け渡し、入力0_ProcessInputRowではそのメンバ変数を用いるようにすることをオススメします。

まとめ

次回はスクリプトコンポーネントでの出力分岐です。