Integration Servicesの実践的な使い方
本連載では第1回「Integration Servicesで異種データも楽々インポート 」、第2回「BI Development StudioでETL処理を開発する 」で、「Integration Services」の概念から基本的な利用法までを紹介してきました。Integration Servicesがさまざまな形で企業に存在するデータ群を取り扱ううえで、非常に優れたサービスであることを感じ取れたと思います。
前回は「BI Development Studio」を利用して単純なCSVデータの取り込みを実装しましたが、今回は次の3点の機能を前回作成したパッケージに加えたいと思います（ここで使用するパッケージの内容は前回「BI Development StudioでETL処理を開発する 」を参照してください）。
- エラーが発生した場合、管理者へメールを送信する
- パッケージの実行に関するログを生成する
- 読み込みに失敗したデータを出力する
Integration Servicesで作成したパッケージは、多くの場合、日次や週次のような形で定期的に自動で実行される運用が想定されます。このような利用法においては、パッケージの実行状況や問題発生に対する監視が非常に重要となります。パッケージの実行が失敗していることに気付かず、問題のあるデータをそのまま利用してしまった、というような問題は何としても避ける必要があります。このような問題に対処するために、Integration Servicesでは「イベントハンドラ」「ログの出力」「エラー出力」がサポートされています。まずは、イベントハンドラから見てみましょう。
イベントハンドラの実装
Integration Servicesはタスク実行時に発生するエラーや警告などの、イベントに対する処理をイベントハンドラに定義することができます。イベントハンドラでは制御フロー、データフローのそれぞれを定義することが可能であるため、エラーの内容をメールで送信したり、一次データの削除を行うなど、各種イベントに対する柔軟な処理を定義できます。
イベントハンドラは、これまでと同様にBI Development Studioを利用して定義します。前回の記事で作成したプロジェクトにイベントハンドラを定義して、エラーに対するメールを送信する処理を定義してみましょう。
イベントハンドラは、「イベントハンドラ」タブより設定を行います。イベントハンドラを定義する対象を「実行可能ファイル」の一覧から選択します。データの取り込み処理を監視したいので、対象を「データフロータスク」とします。続いて「イベントハンドラ」は「OnError」を選択します（図1）。
「イベントハンドラ」は「OnError」以外にも主に次のようなイベントを利用することができます。
| イベントハンドラ | イベント内容 |
| OnError | エラー発生時に発生 |
| OnExecStatusChanged | 実行状態が変化したときに発生 |
| OnPostExecute | 実行完了直後に発生 |
| OnPreExecute | 実行される直前に発生 |
| OnTaskFailed | タスクが失敗したときにタスクから発生 |
| OnWarning | 警告の発生時に発生 |
| 表1 イベントハンドラの種類 | |
画面中央のリンクをクリックしてイベントハンドラの定義を開始します。「ツールボックス」より「メール送信タスク」をドラック＆ドロップし、メール送信の定義を行いましょう。
メール送信タスクは、変数を利用した動的なメッセージ作成を行うことができますが、ここでは固定のメッセージを送信する設定とします。
| 項目 | 値 |
| SmtpConnection | SMTP接続マネージャを新規に作成する |
| From | 送信元のメールアドレスを指定する |
| To | 送信先のメールアドレスを指定する |
| Subject | 件名を指定する |
| MessageSourceType | ソースの種類を「直接入力」「ファイル接続」「変数」から選択する |
| MessageSource | メールの本文を指定する |
| 表2 メール送信タスクの設定 | |
以上の設定で、「エラーが発生した場合、管理者へメールを送信する」が実現できました。これでパッケージ運用中に問題が発生した場合、管理者が問題に気付くことができます。次は問題を特定するために必要となるログの出力です。
ログ出力の定義
Integration Servicesでは、パッケージの実行に関する各種情報をログとして出力することが可能です。例えば、パッケージの開始日時や終了日時をログとして記録するといったことが可能です。Integration Servicesでは、ログの出力方法や形式を定義する「ログプロバイダ」として次のようなものがあります。
| ログプロバイダ | 説明 |
| テキストファイル | ログをカンマ区切りのCSV形式で出力する |
| SQL Server Profiler | SQL Serverプロファイラで表示可能なログファイルを作成する |
| SQL Server | SQL Server 2005データベースのsysdtslog90テーブルにログを書き込む |
| Windowsイベントログ | ローカルコンピュータ上のWindowsイベントログのアプリケーションログにログを書き込む |
| XMLファイル | XMLファイルにログを書き込む |
| 表3 ログプロバイダの一覧 | |
ここでは、扱いやすいテキストファイル形式でログを出力したいと思います。BI Development Studioのメニュー「SSIS」の「ログ記録」から「SSIS ログの構成」ダイアログボックスを呼び出し、ログプロバイダの設定を行います（図2）。
ログを生成する対象となるパッケージを選択し、プロバイダの種類は「テキストファイルのSSISログプロバイダ」を選択し、「追加」ボタンをクリックしてログプロバイダを追加します。ログプロバイダは複数定義することが可能です。あるタスクは処理の開始と終了を、別のタスクはエラーの発生を保存、というようなログ記録が可能です。
ログのテキストファイル出力では対象のファイルを「ファイル接続マネージャ」から指定します。ログプロバイダの「構成」列から＜新しい接続...＞を選んで「ファイル接続マネージャ」ダイアログを呼び出し、ファイルの指定を行ってください。ファイルの物理パスとともにログファイル名を設定します。
ログの出力内容については「詳細」タブを開き、「詳細設定」ボタンを選択します。ここでは対象のイベントごとにログスキーマを定義できます（図3）。ログスキーマは下記から選択します。
| 要素 | 説明 |
| Computer | ログイベントが発生したコンピュータの名前 |
| Operator | パッケージを起動したユーザーのID |
| SourceName | ログイベントが発生したコンテナまたはタスクの名前 |
| SourceID | ログイベントが発生したパッケージ、タスクなどの一意識別子 |
| ExecutionID | パッケージ実行インスタンスのGUID |
| MessageText | ログエントリに関連付けられるメッセージ |
| DataBytes | ログエントリ固有のバイト配列 |
| 表4 ログスキーマ | |
イベントとスキーマの組み合わせで多様なログの保存構成を作成できます。ただし、過剰なログの記録はディスク容量の圧迫やパフォーマンスに影響を与えてしまうので注意が必要です。例えば、パッケージの開始と終了は簡潔に、エラー発生時は詳細に出力、といった形です。保存構成はテンプレートとしてXMLファイルに保存できるので、よく利用する構成を保存しておけば、ログ内容の標準化も行えるでしょう。
実際にログを出力した結果は図4のような形になります。パッケージの実行サーバやユーザー、実行の開始終了日時などとともに、データフロータスクでOnErrorが発生していることが分かります。
以上で「パッケージの実行に関するログを生成する」が実現できました。ログ出力によって、どこで問題が発生したのか確認できるようになったので、後は実際のエラーデータを確認できるようにしましょう。
エラー出力による行のリダイレクト
最後に「読み込みに失敗したデータを出力する」を実現したいと思います。Integration Servicesでは各種データベースやExcelファイル、CSVファイルなど、さまざまなデータソースを利用します。このような異なる環境間でデータをやりとりする場合、予期しないデータによるエラーが多く発生します。数値型で設定したフィールドに文字列が含まれていた場合や、列長が異なる場合などです。このような問題が発生した場合に、膨大なデータの中から問題個所を発見するのは非常に手間のかかる作業です。そこでIntegration Servicesでは、多くのデータフローコンポーネントで「エラー出力」がサポートされています。エラー出力を利用すれば、行レベルのエラー処理が可能となります。
BI Development Studioでは、タスク同士を接続する際、成功パス（緑の線）とエラーパス（赤の線）を利用します。通常は成功パスを使用してフローを設計しますが、エラーパスを利用すれば、エラー出力などを利用してエラー発生時の対応を記述できます。
今回は、データ取り込み時にエラーが起こった場合に対応したいので、データフローの「商品マスタ」フラットファイルソースタスクに対してエラー出力の設定を行います。「フラット ファイル ソース エディタ」ダイアログより「エラー出力」を選択し、各行のエラーを「行のリダイレクト」とします。
次に、エラーデータの保存先を指定します。ツールボックスより「フラットファイル変換先」を画面上に配置し、「商品マスタ」から出るエラーパスを接続します。タスク名を「エラーデータ保存」としました。続いて「エラーデータ保存」より「フラット
ファイル変換先エディタ」にて保存先ファイルの設定を行います。「接続マネージャ」でエラーファイル用に新規ファイルを作成してください。
以上で、指定した保存先にエラーのあったデータ行が出力されます。図7のように数値が入るべきフィールドに文字列が入っていた場合、図8のようにエラーデータに加え、ErrorCode列とErrorColumn列が出力されます。
以上で、「読み込みに失敗したデータを出力する」という目的の機能を実現することができました。
これで、エラーの検知、動作状況の確認、エラーデータの特定まで一連の問題解決に向けた機能を実装することができました。実運用において、これらの機能が備わっているといないでは、大きな差が生まれるのではないでしょうか。
ETLサービスというと、各種データソースとの連携やGUIによる使い勝手など派手な部分に目がいってしまいますが、今回のような裏方ともいえる部分も非常に重要です。自動で実行され、さまざまなデータを取り扱う性格上、実行状況の監視やエラーの出力、ログの保存は欠かすことのできない要素でしょう。Integration Servicesを利用した開発を行う際は、意識してこれらの機能を利用するよう心掛けてください。
