この記事でわかること

システム構築を行う際、前システムからのデータ移行や別で動作しているシステムからのデータ連係など、システム単体で完結せず、複数のシステムとのデータのやりとりが必要になるケースが多く発生します。

そんな時に活用できるのがETLツールです。

今回はその中で、SSISとよばれるツールをご紹介します！
「SSIS」を初めて聞かれた方や、名前だけは聞いたことあるけれど、上手く使うにはどうすればいいかわからない方向けに、環境構築から動作・実行時のポイントをご説明します。

目次

  • SSISとは！？
  • SSISの環境構築
  • SSISでのプログラミング
  • ポイント集
    • ポイント１：シーケンスコンテナを上手く使う！
    • ポイント２：Excel読込時のヘッダー名に注意！
    • ポイント３：データ変換は必ず実施！
    • ポイント４：自動文字列判断は変更不可なので注意！
    • ポイント５：とにかくパラメータ化！
  • まとめ

SSISとは！？

SSISというツール、みなさんご存じでしょうか？

正式名称は「SQL Server Integration Services」という名前です。名前の中に入っているとおり、米マイクロソフト(Microsoft)社のデータベース管理システム「SQL Server」に付随するETLツールの一つです。

Microsoft SQL ServerのEnterpriseEdition及びStandardEditionを購入した際に同梱されているツールとなります。そのため、ExpressEditionでは利用できないので注意が必要です。

基本は、Microsoft SQL Serverをスムーズに稼働させるために別のDBやファイルからデータを加工し取り込むことが目的となっています。もちろん、逆の場合にも利用できます。

気づかれている方もいると思いますが、取り扱えるデータ形式にMicrosoft Office製品を指定することが可能です。

データベースに格納するほどでもないデータをExcelで管理している場合など、Excelファイルをソースファイルとしてデータベースに移行させることが可能です。

CSVファイル等に出力する手間が省けると考えると、非常に便利なツールです！

SSISの環境構築

実際にSSISを利用するにあたっては、Microsoft SQL Serverをインストールすることに加え、加工手順などを指定する際の設定ツールとしてMicrosoft Visual Stadioを利用します。

今回は、Microsoft SQL Serverは既にインストールされており、開発環境として、Microsoft Visual Stadaio2017をインストールする際の手順から説明します。

  • VisualStadioのインストール
    1. インストール対象以外の選択以外はデフォルト指定します。
    2. インストール対象機能には「データの保存と処理」にチェックを付けます。
      ※ インストール詳細では、「SQL Server Data Tools」にチェックされているのを注意します
      具体的には、下図「インストール対象」を参照
    3. インストール完了まで待ちます。
インストール対象
  • VisualStadioの拡張設定
    1. 「SSDT-Setup-JPN.exe」を実行し、インスタンスに先ほどインストールしたVisualStadioを選択します。
      なお、このタイミングで「SQL Server Integration Services」がチェックされている事を確認します！
      具体的には、下図「Microsoft SQL Server Data Toolsのインスタンス設定」を参照
    2. インストール完了まで待ちます。
Microsoft SQL Server Data Toolsのインスタンス設定
  • Excelの取込準備
    1. https://www.microsoft.com/ja-jp/download/details.aspx?id=13255 サイトから、Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをダウンロードします。
    2. 「AccessDatabaseEngine.exe」を起動しインストールします。
      設定はデフォルトのままです。

以上で、SSISを使う開発環境は整いました。なお、SQLServer以外のデータベースと接続される場合は、対象のデータベースクライアントの接続が必要となります。
お使いになりたい環境に合わせて設定しましょう！

SSISでのプログラミング

SSISの開発では単純な取込ですと、１文字もコードを記載せずにデータを移行させることが可能です。ツールバーから用意されたタスクをD&Dし、設定するだけになります。

では、実際の画面を見てみましょう。
基本的な構造はフローチャートのように１つ１つのタスクが矢印で結ばれ、タスクが完了すれば先のタスクに進んでいくという流れになります。
（具体的な画面は、下図「SSISフロー図」を参照）

SSISフロー図

ちなみに、図「SSISフロー図」では「SQL実行タスク」の処理で【テーブルの中身を削除】し、「データフロータスク」で【元データ（CSVやOfficeファイル）から読み込みとDBへの書き込みを行う】流れを表しています。

実際に「SQL実行タスク」での、実行するSQLについても「SQL実行タスク エディター」ダイアログが用意されており、指定された箇所に設定値を入力することで指定が可能となります。

SQL実行タスク エディター

次に「データフロー」タスクの中身ですが、こちらもフローチャートのように設定が可能です。

データフロータスク

ちなみに、この画面ですが「→矢印」が読込、「←矢印」が書き込みと言う基本を押さえておくと、この画面が何を定義しているかを視覚的に判別することができます。
また、「フラットファイルソースタスク」と「OLE DB変換先タスク」に囲まれた「データ変換タスク」では、文字サイズを変更したり、文字列を数値に変えることが可能です。
例えば、CSVには頭ゼロ付き4桁で定義されているが、DBには数値で管理している場合などに変換を行います。

とにかくSSISの開発のメリットの1つがVisualStadioで行えることで、.NETプログラマなど多くのプログラマが慣れ親しんだツールを使い開発できる点は大きいメリットです。
もちろん、VisualStadioを使っているということは、既定のタスクで処理が行えない場合でも、C#やVB.netの言語でプログラムを書くことが出来る「スクリプトタスク」も用意されています。
このためSSISでは基本的に処理できないものはありません！

SSISを利用する上で、押さえておくと便利なポイントがいくつかあるので、以降で紹介させていただきます。

ポイント集

ポイント１：シーケンスコンテナを上手く使う！

タスクを置いていくだけで取込処理を手軽に作れますが、処理によっては２つのINPUTを元にデータを作成するということがあります。単純に並べたままだと、どちらか片方が完了した瞬間に次のタスクが実行されてしまいます。
こんな場合に利用するのが「シーケンスコンテナ」です。コンテナ内にタスクを置くことで、コンテナ内の全てのタスクが完了しないと次に進めない制約を加えることができます。

ポイント２：Excel読込時のヘッダー名に注意！

　Excelファイルを入力ファイルに設定した場合、１行目に設定されているセル情報を列名として自動設定ができます。
ヘッダー名を列名とすることで、書き込み時の指定も列名を利用することが出来るので、指定していない場合の「列１」よりは、視覚的に何のデータなのか分かりやすいため保守性も向上します。
ですが、このヘッダー名を列名とした場合、入力ファイルのヘッダー名は1文字たりとも変わってはいけないという制約が発生します。
１回限りのデータ移行であれば問題ないでしょうが、定期取込の月次更新データなど取込データExcelを手動作成している場合など、うっかりヘッダー名に空白文字が入力されてしまうと見た目変わりませんが、SSIS上エラーにある場合があります。
（これ、ヘッダー名を元に列を特定しているためです）
もし、入力ファイルがExcelで月次取込など定期的な取込ツールとして利用する場合は、基本ヘッダーをExcelのシート保護機能を利用し変更できないようにすることがエラー発生の可能性を減らせます。

ポイント３：データ変換は必ず実施！

　SSISは賢いプログラムなので、基本的には通常の.NETプログラムのように暗黙変換を行ってくれます。
ですが、他のプログラムの暗黙変換と同じように完璧な精度での変換を行ってくれない可能性があります。
そのためSSISには「データ変換タスク」が用意されており、読み込んだ列のデータを任意の型やサイズに変更することができます。
指定も列ごと指定できるため、柔軟な指定が可能となり、意図しないエラーを回避することができます。
特にサイズは重要で、サイズの切り捨てが発生しただけでエラーとなることがあるので、必ず設定しておくべきです。

ポイント４：自動文字列判断には注意！

　ExcelやCSVを読込元とした場合、設定されているデータを元に型やサイズを自動判定されます。たとえば「2020/1/1」の列があれば日付型と判断される、といった感じです。
実はこれには落とし穴があり、指定された行数で自動判断（初期値は200行）されてしまいます。
先ほどの例ように日付型と判断された列に、実は数値や文字列が後続行に入っていたとしても、無理矢理日付型として読み込もうとします。
そのため、データがきれいに整形されていない場合にエラーが発生する可能性が高まります。
一番効果的な対応は、入力データのクリーニングですが、SSIS側でも対応できる数少ない対応をお伝えしようと思います。
この場合、対処方法としては２パターン存在します。

  1. データの並びを変更する
    自動判定は先頭数行を用いて判断しているので、データの並びを変更することで自動判別の型を変更できます。
  2. 初期読み込み行を大幅に増加する
    初期読み込み行を増やすと、指定された行で自動判断してくれます。よって、ファイルの最大行数を指定した場合、自動判断は正しい値になりますが、最終行に合算行など取り込まない行がある場合は、注意が必要です。

この２つは、個々の状況によって、どちらが最適か決まると思われるので、状況に応じて設定します。

ポイント５：とにかくパラメータ化！

SSISでは、画面上で設定できる内容をパラメータとして外部ファイルで指定することができます。
パラメータ化は通常のプログラムでも重要なようにSSISでも重要な機能で、環境毎に異なる設定を、プログラムとは切り離して管理することが出来ます。
これによりSSISプログラム本体を変更することもなく環境固有の設定を切り替えることができ、構成管理上管理しやすくなります。

まとめ

SSISは少しクセはありますが、簡単に移行プログラムを設定することが出来るため、初心者プログラマやプログラマ以外でも簡単に構築・活用できるツールです。
特に単純な移行だと、既定タスクの組み合わせだけで作成ができ、仮に複雑な部分があっても、プログラムも利用したタスクを用意することで対応できるため、開発期間を大きく削減していくことができると思います。
ただ、WEB上にSSISに関する情報がまだ少ないです。これからこの記事のようなTechnical解説サイトが増えていくでしょう。これからSSIS人口も増え、更に磨きがかかったツールになると思います。