1
■SSISの概要
SQL Server Integration Services（略称「SSIS」：データ統合サービス）は、Microsoft SQL Serverに装備された“データ転送ツール”で、SQL Server 2000以前ではDTS（Data Transformation Services：データ変換サービス）と呼ばれていたものをより高度にしたツールです。
SSISを利用することで、SQL Serverのテーブル間のデータ転送はもとより、Access、Oracle、ODBC対応のデータベース、さらにはExcelファイルやCSVファイル・固定長テキストファイルなど、さまざまなデータソースからSQL Serverへとデータを取り込んだり、あるいはその逆にSQL Serverの方からそれらへデータを出力したりすることができます。
さらに特徴的なものとして、bcpコマンドなどは比較的単純なデータコピー機能だけですが、SSISを使うと次のようなより高機能なデータ転送を行うことができます。
- 単純なテーブル間のコピーだけではなく、データを変換・加工しながら転送できる
- 転送の事前・事後にSQLコマンドを発行することができる
- SQLコマンドとしてストアドプロシージャを実行することもできる
- Visual Basic.NET/C#に準じたスクリプトを組み合わせることで非常に細かい変換処理を実装できる
- エラー処理を実装できる
- 処理成功の情報や転送（あるいは変換）エラーの発生したレコードの情報などをメール送信できる
- FTP処理と連動したデータ転送ができる
- その他、より高度なさまざまなタスクやコンポーネントが用意されている
- SQL Server Agentを利用することで自動夜間処理などを実現できる
■開発の基本フロー
それでは、まずはSSISでどのようにして仕組みを作っていくかを、簡単な処理を例にとってそのおおまかな手順を説明していきたいと思います（細かい操作手順については別の回で説明します）。
- SQL ServerとSQL Server Integration Servicesのサービスの起動
SSISを利用するには、「SQL Server」と「SQL Server Integration Services」のサービスを起動しておく必要があります。Integration ServicesはSQL Server本体とは別のサービスとなっていることに注意してください。
通常、本格的に運用される場合は自動起動になっているはずですので特に気にすることはないかもしれませんが、もしテスト環境などでサービスが未起動の場合は、その両方を起動しておきます。
なお、SQL ServerとIntegration Servicesは別のサービスとなっているだけでなく、インストール時も別扱いの機能となっています。SQL Server本体は「データベースエンジンサービス」、Integration Servicesは「共有機能」という位置付けになっています。もしサービスの一覧にIntegration Servicesがない場合は追加インストールしてください。
- 次で説明するBusiness Intelligence Development Studioも、共有機能のひとつとしてインストールしておく必要があります。
- Business Intelligence Development Studioの起動
Business Intelligence Development Studioは、一連のデータ転送フローをビジュアルな環境で設定するための開発用ツールです。データ転送などのタスクの配置やその詳細内容の設定、複雑な変換処理の設定、その他データ転送に前後した関連処理（SQLの実行やメール送信等）も含めたジョブ全体のフローをデザインすることができます。外観的にはVisual StudioのIDEと同じで、ブレークポイントの設定やステップ実行などのデバッグ機能も実装しています。
このツールを起動するには、次のような操作を行います。
[スタート］メニューの［すべてのプログラム］から、［Microsoft SQL Server 2008］を選択し、［SQL Server Business Intelligence Development Studio］をクリックします。
- ご覧のようにタイトルバーに「Microsoft Visual Studio」と表示されるのは、Business Intelligence Development StudioがVisual Studioと統合された開発環境になっているためです。
- Business Intelligence Development Studioは「SSISデザイナ」ツールとも呼ばれます。以降は「SSISデザイナ」と表記します。
- プロジェクトの作成
SSISデザイナを起動したら、まず「プロジェクト」を新規作成します。
プロジェクトとは言っても、Visual Basic.NETなどのおけるプロジェクトとは多少概念が異なります。Visual Basic.NETなどでは、そのアプリケーションで使うさまざまなフォームやプログラムをひとつのプロジェクトとして管理します。そしてそれをコンパイル＆ビルドすることによってひとつのEXEファイルを生成します。
一方、SSISにおけるプロジェクトは複数の「パッケージ」（ひとつの一連のフローを実行する単位で、拡張子は「.dtsx」ですが一般的なEXEファイルのようなイメージのものです）の集合体に過ぎません。単に個々のソースをひとつの画面で管理するだけです。まったく関係のないパッケージを同じプロジェクトに含めてもかまいません。たとえば、ある開発者が担当しているジョブをひとつのプロジェクト内に収めることもできますし、大きなシステム単位（営業システムとか経理システムとか）でプロジェクトを分けてもかまいません。要するに、同じプロジェクトに含まれているパッケージであれば、SSISデザイナ上で一元的に管理することができ、簡単に開いたり流用したりすることができるといった程度のものになります。
新しいプロジェクトを作るには、次のような操作を行います。
- メニューより、[ファイル]-[新規作成]-[プロジェクト]を選択します。
- 「新しいプロジェクト」のダイアログが表示されたら、「プロジェクトの種類」から“ビジネス インテリジェンス プロジェクト”を選択し、「テンプレート」から“Integration
Services プロジェクト”を選択します。
さらに、プロジェクト名やファイルを置く場所、ソリューション名を指定します。「ソリューションのディレクトリを作成」にチェックを入れると、「場所」で指定されたフォルダの下にその名前のサブフォルダが作られ、そこに以降作られるこのプロジェクトの各種ファイル類が置かれるようになります。
- 「新しいプロジェクト」ダイアログで[OK]ボタンをクリックすると、指定された条件で新規プロジェクトが生成され、下図のような状態になります。この時点で、デフォルトで「Package.dtsx」という名前のパッケージがすでに作られています。
デフォルトの「Package.dtsx」という名前は変更することができます。それには、ソリューションエクスプローラのウィドウで「Package.dtsx」という名前を選択してF2キーを押すか右クリックで[名前の変更]を選択したあと、変更したい名前を直接入力します。あるいはプロパティウィンドウの「ファイル名」の欄で書き換えることもできます。
- 一度プロジェクトを作ったら、次回以降の作業ではそのプロジェクトを“開く”ことになります。それには、[ファイル]-[開く]-[プロジェクト/ソリューション]メニューを実行し、拡張子「.sln」のソリューションファイルを選択します。また、[ファイル]-[最近使ったプロジェクト]メニューから選択することもできます。
- ひとつの“ソリューション”には複数のプロジェクトを持たせることもできます（[ファイル]-[追加]-[新しいプロジェクト]メニュー）。
- メニューより、[ファイル]-[新規作成]-[プロジェクト]を選択します。
- パッケージの作成
デフォルトで作られたパッケージに加えて、さらに新しいパッケージを追加したい場合には、次のような操作を行います。
- メニューより[プロジェクト]-[新しいSSISパッケージ]を実行するか、ソリューションエクスプローラの「SSISパッケージ」を右クリックして[新しいSSISパッケージ]を実行します。
- それによって自動的に名前が付けられて新しいパッケージが生成されますので、必要に応じてその名前を変更したりします。
- パッケージを削除したい場合には、そのパッケージ名の右クリックで[削除]を実行します。
補足この.dtsxファイルの実体は、UTF-8形式のXMLファイルです。中味の構造が理解できればテキストエディタを使って編集することもできます。通常はそのようなケースはあまりないかもしれませんが、ある文字を一括して他の文字に変更したいような場合には、SSISデザイナ上で個別に設定変更するよりもエディタで一括置換した方が楽な場合もあります。またテーブル等のフィールド名を大量に定義するような場合、ひとつのフィールドをSSISデザイナ上で定義したあと、エディタを使ってコピー＆ペーストしながら他のフィールドを定義していくと効率的な場合もあります。
- メニューより[プロジェクト]-[新しいSSISパッケージ]を実行するか、ソリューションエクスプローラの「SSISパッケージ」を右クリックして[新しいSSISパッケージ]を実行します。
- タスクやコンポーネントの配置と設定
「タスク」とは、DBからDBへのデータ転送、SQL文の発行などといった単一の処理の単位です。「コンポーネント」とは、データ転送処理の中のさらに細かい処理の機能です。パッケージが出来たらその中にさまざまタスクやコンポーネントを配置・設定し、さらに複数のタスク・コンポーネントを繋げることで一連の処理フローを作っていきます。
ここでは簡単な例として、ひとつだけのタスクを配置してみます（テーブルを空にするだけの実際にはないであろう例ですが）。
- まず、タスクを配置するには、SSISデザイナの左の[ツールボックス]というタブをクリックし、「ツールボックス」を表示します。
AccessやVB.NETなどでフォームを作るのと同様に、パッケージでさまざまな処理をさせるためにはこのツールボックスからどのようなタスク・コンポーネントを配置するかがポイントとなります。またツールボックスにSSISがどのようなタスクが用意されているかを知っておくことも重要です。
- ツールボックスが表示されたら、配置したいタスクをデザイナの余白部分にドラッグ＆ドロップします。これによって、デザイナ上にそのタスクを表わすボックスが配置されます。
- タスクのボックスの下に表示されている矢印（コネクタ）は、複数のタスクやコンポーネントを結ぶための線です。多くの場合複数のタスク・コンポーネントを配置しますが、その場合はこのコネクタをドラッグ＆ドロップすることでそれらを結合し、処理の実行順序を設定します。
- タスクをデザイナ上に配置しただけでは何の処理もしてくれません。これはAccessのフォームの部品と同様です。次に、そのタスクに動作内容などのさまざまな属性を設定していきます。
それには、タスクのボックスをダブルクリックするか、右クリックして[編集]を選択します。それによって設定用のダイアログが表示されます。
- これは「SQL実行タスク」の設定画面です。設定画面の内容はタスクによって異なります。
- 次に、ダイアログ上でさまざまな設定を行います。
この例では「SQL実行タスク」を取り上げていますが、このタスクの場合、あるデータソースに対してSQL文を発行します。それに関して接続先やSQL文などを指定していきます。
ここではまず「Connection」というプロパティの入力欄の右にある[▼]をクリックして、「<新しい接続...>」選択します。
- 「OLE DB接続マネージャの構成」ダイアログが表示されたら、[新規作成]ボタンをクリックします。
- 「接続マネージャ」ダイアログが表示されたら、「プロバイダ」の一覧から任意の項目を選択します。SQL Serverに対してSQLを実行するのであればSQL
Server系の項目を選択します。Accessのデータベースファイルであれば、そのデータベースファイルのバージョンに応じて「Microsoft
Jet 4.0 OLE DB Provider」（Access2003系）や「Microsoft Office 12.0 Access Database
Engine OLE DB Provider」（Access2007系）等の項目を選択します。
- 次に、これはプロバイダによって設定項目が異なりますが、そのデータベースに接続するために必要な各種設定を行います。SQL Serverの場合には、サーバー名・ログインの種類・ログインに必要なユーザー名等・データベース名などを設定します。
- 設定が完了したら、[接続テスト]ボタンをクリックして、その設定内容で正しく接続できることを確認します。
- 接続テストに問題がなければ[OK]ボタンをクリックします。
それによって「OLE DB接続マネージャの構成」ダイアログの「データ接続」の欄にそれが追加されます。
- 「SQL実行タスク」の場合には、次にデータベースに対して発行するSQL文（あるいはストアドプロシージャなど）を指定します。それには、「SQLStatement」の欄にSQL文を直接書き込みます。
- このとき、入力欄右端の[...]ボタンをクリックすると、「SQLクエリの入力」ダイアログが表示されます。大きな画面のエディタで長いSQL文を書き込むことができます。
- このとき、入力欄右端の[...]ボタンをクリックすると、「SQLクエリの入力」ダイアログが表示されます。大きな画面のエディタで長いSQL文を書き込むことができます。
- 必要に応じてその他の設定を行います。
このとき、「Name」プロパティを設定すると、デザイナ上に配置されるボックス内の文字列を変更することができます。また、あとで分かりやすくするために、「Description」プロパティに任意の説明文を記述しておくこともできます。
- すべての設定が完了したら、[OK]ボタンをクリックします。これによってエディタの画面が閉じ、「SQL実行タスク」の場合にはひとつのタスクに関する設定作業が完了します。
- タスクの名前が「Name」プロパティで設定した名称に変わっていることが分かります。
- タスクの名前はタスクのボックス内の文字部分のダブルクリック（Accessのラベルやコマンドボタンの標題変更の要領です）や、右クリックの[名前の変更]で変えることもできます。
- タスクのプロパティは、SSISデザイナのプロパティウィンドウでも設定することができます。
- 名前には一部使用できない文字もありますが、自由な長い文章も指定することができます。タスク名だけでは分かりづらいですが、下図のように処理内容を細かく記述することで、のちのメンテナンスにも役立ちます。
補足タスクを配置した直後はタスク名の右に赤いマークの×印が表示されていました。これはそのタスクの設定が未完全もしくは問題があることを表わしています。きちんと設定することによって、上図のようにそのマークが消えます。もし設定内容に何らかの問題がある場合には下図のような表示になりますので、あらためてプロパティ設定を見直します。
補足「OLE DB接続マネージャの構成」ダイアログで新しい接続先を追加すると、その名前がSSISデザイナの「接続マネージャ」のタブにも表示されるようになります。ここでF2キーや右クリックの[名前の変更]を使ってあとから分かりやすい名前に変えることもできます。またこのタブ内の右クリックで新しい別のソースに対する接続を追加することもできます。
- まず、タスクを配置するには、SSISデザイナの左の[ツールボックス]というタブをクリックし、「ツールボックス」を表示します。
- 実行とデバッグ
タスクの配置が完了したら、このパッケージを実行します。それには次のような操作を行います。
- メニューから[デバッグ]-[デバッグ開始]を選択するか、ツールバーから[デバッグ開始]ボタンをクリックするか、F5キーを押します。
- パッケージが実行されると、現在実行中のタスクの色が「黄色」に変わります。
- ひとつのタスクの処理が正常に完了すると、タスクの色が「緑色」に変わります。
- 複数のタスクやコンポーネントが配置されている場合には、上から順番にコネクタにそって黄色と緑色が次々切り替わっていきます。すべてのタスクが緑色になればパッケージ全体が正常に処理されたことになります。
- すべてのタスクが実行されても、SSISのパッケージはまだデバッグ実行モード状態にあります。処理が完了したら[デバッグ]-[デバッグの停止]メニューやツールバーの[デバッグの停止]ボタン、あるいはSSISデザイナのウィンドウ下部のメッセージをクリックしてデバッグ状態を完全に終了させ、デザインモードに戻します。・・・この操作を行わないとデザイン変更などの操作ができません
もし実行中に何らかのエラーが発生した場合には、そのタスクの色が「赤色」表示となります。「出力」ウィンドウにその内容が表示されていますので、確認後、デザインモードに戻し、タスクの設定内容などを確認します。
上記の方法では、すべてのタスクが一気に実行されますが、デバッグ機能として次のようなこともできます。
- 特定のタスクを右クリックして[タスクの実行]を選択することで、そのタスクのみを単独で実行させることができます。
- タスクの右クリックで[無効化]を選択すると、そのタスクをスキップさせて実行させることができます（再度右クリックして[有効化]で元に戻せます）。
- 特定のタスクを選択してF９キーを押すと、そこにブレークポイントを設定することができます。ブレークポイントを設定することで、パッケージ全体を実行したときにそのタスクの直前で処理を一時停止させることができます（再度F9キーでブレークポイントを解除、その他[デバッグ]メニューで一括解除も可）。
- デバッグ実行中は「進行状況」、デバッグ停止後は「実行結果」のタブでパッケージの処理状況やエラー状況などを確認することができます。
- その他にもいくつか機能がありますが、[デバッグ]メニューの各メニューを確認してみてください。この中にはデバッグなしで実行させる操作もあります。
- メニューから[デバッグ]-[デバッグ開始]を選択するか、ツールバーから[デバッグ開始]ボタンをクリックするか、F5キーを押します。
- パッケージの単独実行
Visual Basic.NETなどでのプログラム開発では、Visual StudioのIDE上での作業は主に作成とデバッグをメインとしたもので、最終的にはEXEファイルを生成しそれだけをユーザーに配布します。SSISの場合も同様に、デザイナでの作成が完了したら最終的には単独の「.dtsx」ファイルを配布することになります。このファイルをいくつかの方法で実行させることにより、実運用でのパッケージの処理を行います。- ただしSSISの場合にはデバッグ実行とともに（エラーがなければ）「.dtsx」ファイルが最新の状態になりますので、VBのようにあえてEXEファイルを生成するような操作は必要ありません。
「.dtsx」ファイルを単独で実行するには、次のような方法があります。
- ■パッケージ実行ユーティリティを使った方法
- エクスプローラを使って、ソリューションが保存されているフォルダの「bin」サブフォルダにある「.dtsx」ファイルをダブルクリックします。パッケージ実行ユーティリティが起動され、「パッケージ実行ユーティリティ」ダイアログが表示されますので、内容を確認後、[実行]ボタンをクリックします。
- ■コマンドプロンプトを使った方法
- コマンドプロンプトで下記のようなコマンドを実行します。
- dtexec /FILE "C:\・・・<dtsxファイルのフルパス>・・・.dtsx"
- dtexec /FILE "C:\・・・<dtsxファイルのフルパス>・・・.dtsx"
- ■SQL Server Agentを使った方法
- SQL Server Agentサービスのジョブとして.dtsxを登録して実行させる方法です（事前にSQL Server Agentサービスが起動されている必要があります）。ジョブを新規作成し、「SQL Server Integration Servicesパッケージ」→「ファイルシステム」→「.dtsxファイルのフルパス」を登録します。さらに実行スケジュールを任意に指定することで、デイリーの指定時刻にパッケージを自動実行させることができます。
