SQL Server 2008 自習書シリーズ No.7 Integration Services 入門 Published: 2008 年 3 月 18 日 改訂版 : 2008 年 11 月 17 日 有限会社エスキューエル クオリテゖ

Transcription

1 SQL Server 008 自習書シリーズ No.7 Integration Services 入門 Published: 008 年 月 8 日 改訂版 : 008 年 月 7 日 有限会社エスキューエル クオリテゖ

2 この文章に含まれる情報は 公表の日付の時点での Microsoft Corporation の考え方を表しています 市場の変化に応える必要があるた め Microsoft は記載されている内容を約束しているわけではありません この文書の内容は印刷後も正しいとは保障できません この文章 は情報の提供のみを目的としています Microsoft SQL Server Visual Studio Windows Windows XP Windows Server Windows Vista は Microsoft Corporation の米国 およびその他の国における登録商標です その他 記載されている会社名および製品名は 各社の商標または登録商標です Copyright 008 Microsoft Corporation. All rights reserved.

3 目次 STEP. Integration Services の概要とンストール Integration Services について ンポート / エクスポートウゖザードと SSIS デザナ Integration Services のンストール 自習書を試す環境について...0 STEP. ンポート / エクスポートウゖザードによる単純なデータ転送.... データベースの作成.... テキストフゔルの取り込み.... Access データベース (.mdb) の取り込み....4 Excel データ (.xls フゔル ) の取り込み....5 保存したパッケージ (.dtsx) の実行と編集 データのエクスポート...54 STEP. SSIS デザナの基本操作 作成するパッケージの概要...6. データベースの作成...6. SSIS デザナの起動 データフロータスクによるデータの転送 派生列コンポーネントによるデータ変換 参照コンポーネントによる別テーブルのデータ取得 データビューゕによる転送中のデータの表示...99 STEP 4. パッケージの実行 パッケージの実行 (dtexecui) コマンドプロンプトからのパッケージ実行 (dtexec) パッケージの定期実行 (SQL Server Agent ジョブ ) Integration Services とデータウェゕハウスの関係 ~ETL~ SQL Server 008 のデータウェゕハウス関連のパフォーマンス向上 BI 系のサービス (Reporting Services Analysis Services) SQL Server 008 Integration Services の新機能 おわりに...

4 STEP. Integration Services の 概要とンストール この STEP では Integration Services の概要とンストール方法 自習書を試すため の環境などを説明します この STEP では 次のことを学習します Integration Services の概要 Integration Services のンストール方法 Business Intelligence Development Studio のンストール方法自習書を試す環境について 4

5 . Integration Services について SQL Server 008 Integration Services SSIS データ統合サービス は SQL Server の標準機能とし て搭載されている データの コピー や 変換 などが行える データ転送ツール です SQL Server 000 以前のバージョンでは DTS Data Transformation Services データ変換サービス と呼ばれていました Integration Services を利用すると SQL Server 同士でのデータ転送はもちろん Oracle や DB Microsoft Office Access そのほかの ODBC 対応のデータベース Microsoft Office Excel フゔ ル 可 変長のテキスト フゔ ル カンマ区切り タブ区切り 固定長のテキスト フゔ ルなど さまざまなデー タソースから SQL Server へデータを取り込んだり それとは逆に SQL Server からデータを書き出した りすることができます Integration Services は データ転送 変換ツール Excel フゔ ル Accessフゔ ル テキスト フゔ ル さまざまなデータソースとの間で データのコピーと変換が可能 メ ン フレーム 汎用機 Oracle DB MySQL など Integration Services によるデータ転送 変換の例 Integration Services を利用すると 次のようなさまざまなデータ転送が行えます 台の SQL Server の間でデータのコピーや変換を行う Excel データ.xls フゔ ル を SQL Server へ取り込む ンポートする Excel フゔ ル SQL Server 008 データ コピー 5

6 データを変換して転送する データを変換して転送する 姓 と 名 を文字列連結して 氏名 へ変換 性別コード の を 男性 へ を 女性 へ変換 部門番号 を 部門名 へ変換 Access データベース (.mdb フゔル ) を SQL Server へ取り込む Access データベース (.mdb) SQL Server 008 コピー Oracle データベースを SQL Server へ取り込む Oracle データベース SQL Server 008 コピー SQL Server のデータを Oracle へコピー / 変換する SQL Server のデータをカンマ区切りのテキストフゔルへ書き出す ( エクスポートする ) Oracle データベースを Excel フゔルへコピーする (SQL Server とは関係のないところでのデータ転送も可能 ) このように Integration Services を利用すると さまざまなデータベースまたはフゔルとの間で SQL Server とデータ転送が行えるので 色々な場面で役立ちます 特にデータを変換 ( データを加工しながら転送 ) できる機能は 非常に便利で データウェゕハウス (DWH:Data Warehouse) を構築する際には 欠かせないツールになります Integration Services とデータウェゕハウスとの関係については STEP 4.4 で説明しています 6

7 . ンポート エクスポート ウゖザードと SSIS デザ ナ Integration Services SSIS には ンポート エクスポート ウゖザード と SSIS デザ ナ とい う ２つの機能があります ンポート エクスポート ウゖザード ンポート エクスポート ウゖザードは SQL Server Management Studio または Business Intelligence Development Studio から起動でき ウゖザード形式 対話形式 で指定された項 目を入力 選択するだけで簡単にデータの転送 コピー が行えるツールです ンポート / エクスポート ウゖザード このウゖザードの利用方法は STEP で説明します 非常に簡単にデータ転送が行えるので ぜ ひ試してみてください SSIS デザ ナ SSIS デザ ナは 中 上級者向けのツールで Business Intelligence Development Studio の Integration Services プロジェクトから利用します ンポート エクスポート ウゖザードでは 単純なデータ転送しか行えませんが SSIS デザ ナを利用すれば データの転送に加えて 複雑 な変換処理を追加したり 転送の前後に行いたい処理 FTP によるフゔ ル取得やメール送信 SQL の実行など を追加したりすることができます 7

8 SSIS デザナの利用例 さまざまなタスクを実行可能 FTP でフゔルを取得したり 複数のフゔルをまとめて処理する Foreach Loop メールを送信したりすることもできる また SSIS デザナには デバッグ機能も充実していて データ転送中のデータを確認したり ブレークポントを設定して ステップ実行 ( 配置したコンポーネントを つ つ実行 ) をしたり 実行中の変数の値を確認したりすることも可能です SSIS デザナはデバッグ機能も充実 変換 転送中のデータを確認できるので 正しく実行されているかを容易に確認できる ブレークポントを設定してステップ実行できる また 変数に格納された値を確認することも可能 エラー発生時にスクリプトを実行するように変更することも可能 SSIS デザナの使用方法ついては STEP.5 以降で説明しています 8

9 . Integration Services の ンストール Integration Services の ンストール Integration Services のすべての機能を利用するには SQL Server 008 の ンストール時のコ ンポーネントの選択画面で次のように選択します SQL Server データベース エンジンと Agent ジョブ 機能 Integration Services のパッケージを開発するツー ルとなる Business Intelligence Development Studio SSIS デザ ナ を ンストールする場合 に必要 STEP２.５以降で説明 Integration Services のサーバー機能 パッケージ の実行や管理 パッケージ ンストール ウゖザー ド機能など に必要 SQL Server の管理ツールである Management Studio やプロフゔ ラ 構成マネージャなど Management Studio を利用して パッケージの ジョブ登録や SQL Server 上に配置したパッケー ジの管理をするために必要 Business Intelligence Development Studio は SSIS デザ ナ を利用して Integration Services のパッケージ データ転送を設定したフゔ ル を開発するために必要なツールです Integration Services は サーバー機能 パッケージの実行や管理 パッケージ ンストール ウゖザード機能など を利用するために必要となるサービスです 詳しくは 本自習書シリーズの Integration Services 応用 で説明します 管理ツールは SQL Server の管理ツールである Management Studio やプロフゔ ラ 構成マ ネージャなどが ンストールされ Management Studio を利用して パッケージのジョブ登録や SQL Server 上に配置したパッケージを管理するために必要になります データベース エンジン サービスは SQL Server 008 のデータベース エンジンと SQL Server Agent サービスです パッケージを定期実行したい場合には Agent サービスのジョブ機能を利 用します STEP 4 で説明します SQL Server 008 Integration Services の新機能について SQL Server 008 の Integration Services で提供される新機能については この自習書の巻末 にまとめているので 新機能のみを知りたいという方は そちらをご覧ください 9

10 .4 自習書を試す環境について 必要な環境 この自習書の手順を試すために必要な環境は次のとおりです OS Windows Server 00 SP 以降 または Windows XP Professional SP 以降 または Windows Vista または Windows Server 008 ソフトウェゕ SQL Server 008 Enterprise / Developer / Standard Edition この自習書の手順をすべて試すには 次のコンポーネントを ンストールしておく必要が あります 詳しくは 前のページを参考にしてください データベース エンジン サービス Integration Services Business Intelligence Development Studio 管理 ツール この自習書内での画面やテキストは OS に Windows Server 00 SP ソフトウェゕに SQL Server 008 Enterprise Edition を利用して記述しています サンプル スクリプト この自習書を試すには サンプル スクリプトをダウンロードしておく必要があります サンプル ス クリプトには 各 STEP の ンポートの実習で使用するフゔ ル CSV フゔ ルや Excel フゔ ル Access データベースなど が含まれています 0

11 STEP. ンポート エクスポート ウゖザードに よる単純なデータ転送 この STEP では Management Studio から ンポート エクスポート ウゖザードを 利用して テキスト フゔ ル CSV フゔ ル や Excel フゔ ル.xls Access デ ータベース.mdb を SQL Server へ取り込んだり それとは逆に SQL Server のデ ータをテキスト フゔ ルへ書き出したり パッケージとして保存したりする方法につい て説明します この STEP では 次のことを学習します テキスト フゔ ルの取り込み ンポート Access データベース.mdb フゔ ル の取り込み Excel データ.xls フゔ ル の取り込み パッケージの保存と実行 編集 データの書き出し エクスポート

12 . データベースの作成 データの取り込み先となるデータベース SSISdb の作成 この STEP では ンポート エクスポート ウゖザードを使用して さまざまなデータを SQL Server 内へ取り込んでいくので まずは 取り込み先 転送先 となる空のデータベースを作成 します 次のように SQL Server Management Studio から データベース フォルダを右クリ ックして 新しいデータベース をクリックし SSISdb という名前のデータベースを作成し ます データベース名に SSISdb と入力 データベース を右クリック して 新しいデータベース を クリック [OK] ボタンをクリック

13 . テキスト フゔ ルの取り込み 取り込むテキスト フゔ ル ここでは Integration Services の ンポート エクスポート ウゖザードを利用して 次のよう な CSV フゔ ル カンマ区切りのテキスト フゔ ル を SQL Server へ取り込む ンポート する 手順を説明します このフゔ ルは サンプル スクリプト内に Shohin.csv という名前 で置いてあります 実行手順. ンポート エクスポート ウゖザードを利用してデータを ンポートするには 次のように Management Studio で取り込み先となるデータベース 今回は SSISdb を右クリック して タスク メニューの データの ンポート をクリックします. すると 次のように ンポート エクスポート ウゖザードが起動するので 次へ ボタンを クリックします

14 . 次の データソースの選択 画面では データの転送元を選択します 転送元がテキスト フ ゔ ルの場合は データ ソース で フラット フゔ ル ソース を選択します 転送元となるフゔ ル を選択するためにココ をクリック 次に 参照 ボタンをクリックして 転送元となるフゔ ルを選択します 次のように フ 4

15 ゔ ルの場所 でサンプル スクリプトを展開したフォルダを選択して フゔ ルの種類 で すべてのフゔ ル *.* を選択します サンプル スクリプトを 展開したフォルダを選択 サンプル スクリプト内 の Shohin.csv フゔ ルを転送元のフゔ ル として選択 フゔ ルの種類で すべてのフゔ ル *.* を選択 4 フォルダ内のすべてのフゔ ルが表示されたら Shohin.csv フゔ ルを選択して 開く ボタンをクリックします データソースの選択 画面へ戻ったら 次のように フゔ ル名 へ Shohin.csv への パスが表示されているのを確認し 列 をクリックします 4. 列 をクリッ ク選択 列 ページでは テキスト フゔ ルのデータ形式を設定することができます 5

16 行区切りと列区切りを 確認 プレビューで CSV フゔ ルの中身が表示 される 行区切り記号 に 改行 を表す {CR}{LF} が選択され 列区切り記号 にコンマ{,} が 選択されていることを確認します 今回取り込むフゔ ルは CSV カンマ区切り フゔ ル なので このままの設定で大丈夫です 5 行のプレビュー には 転送元の CSV フゔ ルのデータが表示され 列名が自動 的に 列 0 列 列 と命名されていることを確認できます 確認後 次へ ボタンをクリックします 5. 次の 変換先の選択 画面では データの転送先 取り込み先 を指定します 今回は SSISdb データベースを選択してウゖザードを起動したので すでに データベ ース に SSISdb が選択されていることを確認できます また 変換先 には SQL Server 6

17 Native Client 0.0 が選択されていますが これは SQL Server 008 を転送先にすると いう意味です SQL Server 008 の内部的なバージョン番号は 0.0 なので Native Client 0.0 が選択されています サーバー名 には SQL Server の名前が選択されていること も確認して 次へ ボタンをクリックします 6. 次の コピー元のテーブルおよびビューを選択 画面では 転送元と転送先となるテーブルを 指定します 今回のように転送元がテキスト フゔ ルの場合には 変換元 へフゔ ル パ ス Shohin.csv フゔ ルへのパス が表示されます 転送元がテキスト フゔ ル の場合には フゔ ル パス が表示 データの転送先となるテーブル デー タベース内に Shohin という名前 のテーブルを作成するという意味 フゔ ル名をもとにしたテーブル名 Shohin が自動的に表示される 変換先 へは [dbo].[shohin] と表示されますが これは SSISdb データベース内 へ Shohin という名前のテーブルを作成するという意味です テーブルは 実際にデータ が転送れるときに自動作成されます テーブル名は ここで変更することもできますが 今 回は Shohin という名前のままにしておきます 続いて マッピングの編集 ボタンをクリックします すると 次のように 列マッピング ダ ゕログが表示され 作成されるテーブルの列名やデータ型などを変更できるようになりま す 7

18 転送元の列名 列名を変更 転送先の列名 4 既定では テキスト フゔ ルのデータは varchar データ型として設定され 列名は 左側 から順に 列 0 列 列 と設定されます 今回は 変換先の列名を分かりやすくす るために 上の画面のように 列 0 を 商品番号 へ 列 を 商品名 へ 列 を 単価 へ変更しておきます 変更後 OK ボタンをクリックします 7. コピー元のテーブルおよびビューを選択 画面へ戻ったら 次へ ボタンをクリックしま す 8. 次の パッケージの保存および実行 画面では このデータ転送をすぐに実行するか 後から 実行するために保存するかを選択します 8

19 データ転送をすぐに実行す る場合は ココをチェック 今回は すぐにデータ転送を実行するので すぐに実行する が選択されていることを確認し て 次へ ボタンをクリックします 9. 次の ウゖザードの完了 画面で 完了 ボタンをクリックするとデータの ンポートが開始 されます 0. 実行が完了すると 次の画面が表示されます 9

20 5行のデータが 転送されたこと が分かる 状態 がすべて 成功 と表示されていることを確認して 閉じる ボタンをクリックし ます 取り込んだデータの確認. ウゖザードで ンポートしたデータを確認するには 次のように Management Studio で SSISdb データベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックします 0

21 テーブルの一覧に Shohin という名前のテーブルが作成されていることを確認できます 続いて Shohin テーブルを右クリックして 上位 000 行の選択 をクリックします テキストフゔ ルから 取り込んだ ンポー トした データ これにより Shohin テーブルのデータ 最初の 000 件 が表示されます 表示されたデ ータが 取り込んだテキスト フゔ ル内のデータ 以下 と同じであることを確認します Shohin.csv フゔ ルのデータ

22 . Access データベース.mdb の取り込み 取り込む Access データベース 次に Access 00 で作成したデータベース.mdb フゔ ル を SQL Server へ取り込んでみ ましょう 取り込むフゔ ルは サンプル スクリプト内の Employee.mdb という名前のフ ゔ ルで このデータベース内には 次のように 従業員 テーブルと 部門 テーブルがあり 主キーやリレーション シップを設定しています 実行手順 Access データを ンポートする手順は テキスト フゔ ルを ンポートする手順とほとんど同 じです. まずは 次のように Management Studio で取り込み先のデータベース SSISdb を右 クリックして タスク メニューの データの ンポート をクリックします

23 . ンポート エクスポート ウゖザードが起動したら 次へ ボタンをクリックします. 次の データソースの選択 画面では データ ソース に Microsoft Access を選択し ます サンプル スクリプト内の Employee.mdb フゔ ル を選択 4 5 次に 参照 ボタンをクリックして サンプル スクリプト内の Employee.mdb フゔ ルを選択し 開く ボタンをクリックします フゔ ルの選択後 次へ ボタンをクリックして先に進みます Note Access 007 データベース.accdb を取り込みたい場合 Access 007 のデータベース.accdb フゔ ル からデータを取り込みたい場合は [データ ソース] で Microsoft Office.0 Access Database Engine OLE DB Provider を選択する必要があります このプロバ ダは Microsoft Office 007 の ンストール時に自動で ンストールされます ンポート エクスポート ウゖザード を実行しているマシンに Microsoft Office 007 を ンストールしていない場合は 下記のサ トから 007 Office system ドラ バ をダウンロードして ンストールすることで 利用できるようになります

24 007 Office system ドラ バ: データ接続コンポーネント &displaylang=ja Microsoft Office.0 Access Database Engine OLE DB Provider を利用して Access 007 の.accdb へ接続している例 今回利用している.mdb フゔ ルのように Access 00 以前のバージョン Access のデータベースの場合には [データ ソース] へ Microsoft Access を選択すれば大丈夫です 4. 次の 変換先の選択 画面では データの転送先 取り込み先 を指定します 今回は 事前に SSISdb データベースを選択してウゖザードを起動したので データベ 4

25 ース へ SSISdb が選択されていることを確認して 次へ ボタンをクリックします 5. 次の テーブルのコピーまたはクエリの指定 画面では つ以上のテーブルまたはビュー からデータをコピーする が選択されていることを確認して 次へ ボタンをクリックしま す 6. 次の コピー元のテーブルおよびビューを選択 画面では 転送元として選択した Access デ ータベース内のテーブルが一覧されます 今回は 次のように 従業員 テーブルと 部門 テーブルの両方をチェックして この つのテーブルを転送対象として設定します 7. 続いて 次のように 従業員 テーブルを選択 ハ ラ ト した状態で マッピングの編 5

26 集 ボタンをクリックします 従業員テーブルを選択した状態で [マッピングの編集] をクリック 転送元 の列名 転送先 の列名 データ型 サ ズ これにより 列マッピング ダ ゕログが表示されて 変換元のテーブルの列名と 変換先 となるテーブルの列名が一覧されます ここでは 列の名前やデータ型 サ ズなどを変更す ることもできますが 今回は何も変更せずに内容を確認するだけで OK ボタンをクリック します 8. 前の手順と同じように 部門 テーブルを選択 ハ ラ ト した状態で マッピングの編 集 ボタンをクリックすれば 部門テーブルに関する情報も確認することができます 内容を確認したら 何も変更せずに OK ボタンをクリックします 9. コピー元のテーブルおよびビューを選択 画面へ戻ったら 次へ ボタンをクリックしま す 6

27 0. 次の パッケージの保存および実行 では このデータ転送をすぐに実行するか 後から実行 するために保存するかを選択します 今回はすぐに実行するので すぐに実行する が選択さ れていることを確認して 次へ ボタンをクリックします 選択されていることを 確認. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックするとデータの ンポートが開 始されます 7

28 実行が完了すると 操作は正常に実行されました 画面が表示されます 状態 へ 成功 と表示されていることを確認して 閉じる ボタンをクリックします 取り込んだデータの確認. ウゖザードで ンポートしたデータを確認するには 次のように Management Studio で SSISdb データベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックします テーブルの一覧に 従業員 と 部門 という つのテーブルが作成されていることを確認 できます 続いて 従業員 テーブルを右クリックして 上位 000 行の選択 をクリックします 8

29 これにより 従業員テーブルのデータが表示されます 表示されたデータが Access データ ベース内のデータ 以下 と同じであることを確認します Employee.mdb データベースのデータ 部門テーブルについても 同様に 上位 000 行の選択 をクリックして データを表示し て確認します 9

30 部門テーブルのデータの確認 Note Access 側で設定された主キーやリレーション シップは転送されない Integration Services では Access データベース側で設定された主キーやリレーション シップなどの制約は転 送されません 次のように Management Studio で取り込んだテーブルを展開し 列 フォルダを展開して 列の一覧を表示し キー フォルダや 制約 フォルダを参照すると これらの設定が転送されていないことを 確認することができます 従業員テーブルの列の一覧 主キーと外部キーには鍵のゕ コンが付くが 設定されて いない 主キーと外部キーが一覧され る場所 ここに存在しないこ とからも主キーと外部キーが 設定されていないことを確認 できる 制約が一覧される 場所 ココも空 部門テーブルについ ても同様 したがって 主キーやリレーション シップなどは データの転送後に手動で設定しなければなりません 0

31 .4 Excel データ.xls フゔ ル の取り込み 取り込む Excel データ 次に Excel 00 のデータ.xls フゔ ル を取り込んでみましょう 取り込むフゔ ルは サ ンプル スクリプト内の Shohin.xls という名前のフゔ ルです 実行手順 Excel データを ンポートする手順は Access データベースを ンポートする手順とほとんど同 じです 違うのは データソースの選択 データソースの選択 画面だけです. まずは 次のように Management Studio で取り込み先のデータベース SSISdb を右 クリックして タスク メニューの データの ンポート をクリックします. すると ンポート エクスポート ウゖザードが起動するので 次へ ボタンをクリックし ます

32 . 次の データソースの選択 画面では データ ソース で Microsoft Excel を選択しま す 5 最初の行を列名として扱う設定 チェックが付いていることを確認 サンプル スクリプト内の Shohin.xls フゔ ルを選択 4 6 次に 参照 ボタンをクリックして サンプル スクリプト内の Shohin.xls を選択し 先頭行に列名を含める チェック ボックスがチェックされていることを確認して 次へ ボタンをクリックします Note Excel 007 データ.xlsx を取り込む場合 Excel 007.xlsx フゔ ル からデータを取り込む場合は Excel バージョン で Microsoft Excel 007 を選択しますが Access 007 の場合と同様 Microsoft Office.0 Access Database Engine OLE DB Provider の ンストールが必要になります

33 このプロバ ダは Microsoft Office 007 を ンストールするか 下記のサ トから 007 Office system ドラ バ をダウンロードして ンストールすることで 利用できるようになります 007 Office system ドラ バ: データ接続コンポーネント &displaylang=ja このプロバ ダを ンストールしていない場合は 次へ ボタンをクリックしたときに 次のエラー メッセージが 表示されます 4. 次の 変換先の選択 画面では データの転送先 取り込み先 を指定します 今回は 事前に SSISdb データベースを選択してウゖザードを起動したので データベ ース へ SSISdb が選択されていることを確認して 次へ ボタンをクリックします 5. 次の テーブルのコピーまたはクエリの指定 画面では つ以上のテーブルまたはビュー からデータをコピーする が選択されていることを確認して 次へ ボタンをクリックしま す

34 6. 次の コピー元のテーブルおよびビューを選択 画面では 転送元となるデータの一覧が表示 されます 転送元データのシート名 をチェック 作成されるテーブル名 が表示される 変換元が Excel の場合には Excel フゔ ル内のワークシートの名前に $ マークをつけ たものが一覧されます 今回のフゔ ルはワークシート名を変更していないので 既定の つ のワークシート名 Sheet Sheet Sheet に $ マークを付けたものが一覧されてい ます 今回取り込むデータは Sheet へ入れてあるので Sheet$ をチェックします これにより 転送先のテーブル名 変換先 が [dbo].[sheet$] と表示されます これは SSISdb というデータベースの中に Sheet$ という名前のテーブルを作成 4

35 するという意味です これを 次のように 商品 など分かりやすい名前へと変更します Sheet$ の部分を 商品 へ変更 続いて マッピングの編集 ボタンをクリックして 列マッピング ダ ゕログを表示し 作成されるテーブルのデザ ンを確認します 転送元の列 Excel シートの 行目 のデータが設定される 作成されるテーブルの列名や データ型 サ ズなどを確認 転送元 変換元 の列名には Excel シートの 行目のデータが設定され それをもとに転 送先 変換先 に作成されるテーブルの列名が設定されていることを確認できます データ型は 既定では Excel 内の数値データは float 型 文字データは nvarchar(55) 型 として作成されます 列の名前やデータ型 サ ズなどは 変更することもできますが 今回 は何も変更せずに内容を確認するだけで OK ボタンをクリックします 5

36 7. コピー元のテーブルおよびビューを選択 画面へ戻ったら プレビュー ボタンをクリッ クします すると データのプレビュー ダ ゕログが開いて 転送元となる Excel データの内容を確 認することができます 確認後 OK ボタンをクリックします 8. コピー元のテーブルおよびビューを選択 画面へ戻ったら 次へ ボタンをクリックしま す 9. 次の パッケージの保存および実行 では 次のように すぐに実行する と SSIS パッケ ージを保存する の両方をチェックして データ転送をすぐに実行して かつ後からも実行で きるように SSIS パッケージ として保存しておくようにします 6

37 ココをチェックして データ転送を後からも実行 できるように SSIS パッケージとして保存する 保存先には フゔ ル システム をチェック パッケージの保存先として フゔ ル システム を選択して 次へ ボタンをクリックしま す 0. 次の SSIS パッケージの保存 画面では 保存するパッケージの名前と保存場所を指定しま す 今回は 次のように 名前 パッケージ名 へ 商品 Package と入力し フゔ ル 名 のパスを C:\商品 Package.dtsx へ変更し C:\ ドラ ブの直下へ保存されるよう にします 変更後 次へ ボタンをクリックして 次へ進みます. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックするとデータの ンポートが開 始されます 7

38 実行完了後 状態 がすべて 成功 と表示されていることを確認して 閉じる ボタンを クリックします 取り込んだデータの確認. ウゖザードで ンポートしたデータを確認するには 次のように Management Studio で SSISdb データベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックします テーブルの一覧に 商品 という名前のテーブルが作成されていることを確認します 続いて 商品 テーブルを右クリックして 上位 000 行の選択 をクリックします 8

39 これにより 商品テーブルのデータが表示されます 表示されるデータが Excel フゔル内 のデータ ( 以下 ) と同じであることを確認します Shohin.xls フゔルのデータ 9

40 .5 保存したパッケージ.dtsx の実行と編集 ここでは 前の STEP で保存したパッケージ 商品Package.dtsx を利用して データの転送を再実行す る手順を説明します 保存したパッケージの実行. 保存したパッケージを実行するには エクスプローラから パッケージ フゔ ル C:\商品 package.dtsx をダブル クリックします. ダブル クリック すると 次のように パッケージ実行ユーテゖリテゖ ダ ゕログが表示されます このダ ゕログで 実行 ボタンをクリックすれば パッケージを実行することができます 40

41 実行 ボタンをクリックしてパッケージが実行されると 次のように パッケージ実行の進 行状況 ダ ゕログが表示されますが エラーが発生して データ転送が失敗してしまいます CREATE TABLE のエラー タスクの失敗を表す エラー の行の記述をみると CREATE TABLE [SSISdb].[dbo].[商品] とあり 商 品 テーブルを作成していることが原因でエラーとなっていることを確認できます 前の STEP で ウゖザードからデータ転送を実行したときは 商品 テーブルを作成するこ とができましたが 回目以降のパッケージの実行時には 商品 テーブルが既に存在して しまっているので エラーが発生してしまうのです 確認後 閉じる ボタンをクリックして ダ ゕログを閉じます. パッケージ実行ユーテゖリテゖ ダ ゕログへ戻ったら 閉じる ボタンをクリックして 閉じます 4. 次に データ転送が失敗していることを Management Studio から念のため確認しておきま しょう SSISdb データベースの 商品 テーブルを右クリックして 上位 000 行の 選択 をクリックします 4

42 データが新しく追加されていないことから データ転送が失敗していることを確認できます 保存したパッケージの編集 Business Intelligence Development Studio 次に このパッケージのエラーを解消して 正しくデータ転送が行えるように パッケージの編集 を行ってみましょう パッケージの編集には Business Intelligence Development Studio を利用します 5. Business Intelligence Development Studio を起動するには 次のように スタート メニ ューの すべてのプログラム から Microsoft SQL Server 008 を選択し SQL Server Business Intelligence Development Studio をクリックします 6. Business Intelligence Development Studio は Visual Studio 008 と統合されているの で 次のように Visual Studio 008 が起動します 4

43 フゔ ル メニューの 新規作成 から プロジェクト をクリック 次に フゔ ル メニューから 新規作成 を選択して プロジェクト をクリックし 新 しいプロジェクトを作成します 7. すると 新しいプロジェクト ダ ゕログが表示されるので 次のように プロジェクトの 種類 で ビジネス ンテリジェンス プロジェクト を テンプレート で Integration Services プロジェクト を選択します 4 プロジェクト名 へは SSISstep と入力して 場所 を C:\ へ変更し OK ボ タンをクリックします これにより SSISstep プロジェクトが作成されます 8. 作成されたプロジェクトには 空のパッケージ Package.dtsx が自動作成されているので 今回は これを削除します 次のように画面右側の ソリューション エクスプローラ で 4

44 SSIS パッケージ フォルダの配下にある Package.dtsx を右クリックして 削除 をクリックします すると 次のように削除を確認するダ ゕログが表示されるので OK ボタンをクリックし ます 9. 次に 前の STEP で作成したパッケージ 商品 Package.dtsx をプロジェクトへ追加しま す 次のように ソリューション エクスプローラ で SSIS パッケージ フォルダを右クリ ックして 既存のパッケージを追加 をクリックします 0. すると 既存のパッケージのコピーを追加 ダ ゕログが表示されるので 次のように パ ッケージの場所 で フゔ ル システム を選択し パッケージのパス で... ボタンを 44

45 クリックします 5 4 すると パッケージの読み込み ダ ゕログが表示されるので C:\商品 Package.dtsx フゔ ルを選択し 開く ボタンをクリックします 既存のパッケージのコピーを追加 ダ ゕログへ戻ったら OK ボタンをクリックして閉じます. 次に 追加したパッケージ 商品Package.dtsx をダブル クリックします 追加した 商品Package.dtsx をダブル クリック パッケージのデザ ン画面 SSIS デザ ナ すると パッケージのデザ ン画面 SSIS デザ ナ が表示されて パッケージの編集がで 45

46 きるようになります. このデザ ン画面からは パッケージを実行することもできるので 次のように デバッグ メニューから デバッグ開始 をクリックして パッケージを実行してみます パッケージが実行されると 次のように 準備 SQL タスク タスクが赤くなってエラー となり 実行が中断されます デバッグ モードになります エラーとなり 赤く表示 される デバッグ メニューから デバッグの停止 をクリックして デバッグを終了します. 次に エラーとなったタスクの内容を確認するために デザ ン画面で 準備 SQL タスク をダブル クリックします 46

47 準備 SQL タスク を ダブルクリック SQLStatement プロパテゖの値へ マウスを乗せると ポップゕップが 表示されて SQLステートメントの 内容を確認できる これにより SQL 実行タスク エデゖタ ダ ゕログが表示されるので SQLStatement プロパテゖをクリックし 値 CREATE TABLE へマウスを乗せて ポップゕップを表 示すると SQL ステートメントの内容を確認することができます 表示される SQL ステー トメントは CREATE TABLE [SSISdb].[dbo].[商品] となっていて 商品テーブルを 作成するステートメントであることを確認できます 確認後 OK ボタンをクリックして ダ ゕログを閉じます タスクの無効化 4. この 準備 SQL タスク のエラーを回避するには このタスクを無効化に設定すればよい ので 次のように 準備 SQL タスク を右クリックして 無効化 をクリックします 灰色にかわる これにより 準備 SQL タスク が灰色に変わり このタスクが実行されないようになり ます 5. 次に デバッグ メニューから デバッグ開始 をクリックして パッケージを実行 デバ ッグを開始 します 47

48 緑色に変わると 成功 すると 無効化した 準備 SQL タスク は実行されずに データ フロー タスク デ ータ転送のためのタスク が 緑色 に変わって 実行が成功したことを確認できます 6. デバッグ メニューから デバッグの停止 をクリックしてデバッグを終了します 7. 次に もう一度 デバッグ メニューから デバッグ開始 をクリックして パッケージを 再度実行し エラーにならないことを確認しておきます 緑色に変わると 成功 デバッグ メニューから デバッグの停止 をクリックしてデバッグを終了します 48

49 取り込んだデータの確認 8. 次に パッケージの実行で取り込んだデータを確認しておきましょう Management Studio から 次のように SSISdb データベース内の 商品 テーブルを右クリックして 上位 000 行の選択 をクリックします 4 ウゖザードで実行した 結果 １回目の転送 パッケージを実行した 結果 ２回目の転送 パッケージを実行した 結果 ３回目の転送 パッケージを実行した回数分だけ データが追加されていることを確認できます このように CREATE TABLE ステートメントが実行されないように変更すれば データ転送 を何度も実行できるようになります ただし このままでは パッケージが実行されるたびに データが上書きされずにどんどん追加されていってしまいます データを削除してからデータ転送を行う 上書きする パッケージを実行するたびにデータが追加されていくのを防ぎ 新しいデータで上書きされるよう にするには 次のようにパッケージを編集します 9. まずは 無効化していた 準備 SQL タスク を有効に戻します 準備 SQL タスク を右クリックして 有効化 をクリックします 49

50 0. 次に 準備 SQL タスク の SQL ステートメントを変更します 準備 SQL タスク をダブル クリックして SQL 実行タスク エデゖタ ダ ゕログを表示し SQLStatement プロパテゖの... ボタンをクリックします 準備 SQL タスク を ダブルクリック SQLStatement プロパテゖの... ボタンをクリック すると SQL クエリの入力 ダ ゕログが表示されるので SQL ステートメントを次のよ うに変更します TRUNCATE TABLE 商品 go 商品テーブルのデータ を全削除するためのス テートメントを記述 TRUNCATE TABLE ステートメントは 指定したテーブルのデータをすべて削除する効果が あるので この記述によって 商品テーブルのデータをすべて削除できるようになります ステートメントの記述後 OK ボタンをクリックしてダ ゕログを閉じます. 次に デバッグ メニューから デバッグ開始 をクリックして パッケージを実行し 結 果を確認します 50

51 今度は つのタスクが緑色に変わって パッケージの実行が成功したことを確認できます. デバッグ メニューから デバッグの停止 をクリックして デバッグを終了します データが上書きされていることの確認. パッケージで取り込んだデータを確認するために Management Studio から 商品 テー ブルを右クリックして 上位 000 行の選択 をクリックします テーブルのデータは すべて削除されてから データ転送が行われているので データが上書 きされていることを確認できます このように Business Intelligence Development Studio SSIS デザ ナ を利用すると 5

52 単純なデータ転送だけでなく 任意の SQL ステートメントを実行できるようになります ま た SSIS デザ ナでは データの変換 を追加することもできるので これについては STEP で説明します パッケージの保存 次に 編集したパッケージを保存しておきます 4. パッケージとプロジェクト全体を保存するには 次のように フゔ ル メニューから すべ てを保存 をクリックします パッケージ実行ユーテゖリテゖからの実行 次に Business Intelligence Development Studio で編集したパッケージをエクスプローらから 実行してみます 5. Business Intelligence Development Studio で作成 編集したパッケージは 次のようにプ ロジェクト C:\SSISstep\SSISstep フォルダの下の bin フォルダへ格納されてい ます ダブル クリック 5

53 このフォルダのパッケージ フゔ ル 商品Package.dtsx をダブル クリックして パッケ ージを実行してみましょう 6. パッケージ実行ユーテゖリテゖ ダ ゕログが表示されたら 実行 ボタンをクリックし てパッケージを実行します 今度は エラーが発生せずに 実行が正常に完了したことを確認できます 完了後は 閉じる ボタンをクリックして ダ ゕログを閉じます 7. 次に パッケージで取り込んだデータを確認するために Management Studio から 商品 テーブルを右クリックして 上位 000 行の選択 をクリックします データが上書きされて 正しくパッケージが実行されたことを確認できます 5

54 .6 データのエクスポート ここでは STEP. で Access データベースから取り込んだ 従業員 テーブルを ンポート エクス ポート ウゖザードを利用して tsv 形式 タブ区切り のテキスト フゔ ルへエクスポートする 書き 出す 手順を説明します エクスポートするデータ エクスポートする従業員テーブルは 次のとおりです 実行手順. ンポート エクスポート ウゖザードを利用してデータをエクスポートするには 次のよう に Management Studio で転送元のデータベース SSISdb を右クリックして タスク メニューの データのエクスポート をクリックします 54

55 . すると ンポート エクスポート ウゖザードが起動するので 次へ ボタンをクリックし ます. 次の データソースの選択 画面では データの転送元 エクスポート元となるデータ を指 定します 今回は 事前に SSISdb データベースを選択してウゖザードを起動しているので デー タベース へ SSISdb が選択されていることを確認して 次へ ボタンをクリックしま す 4. 次の 変換先の選択 画面では データの転送先 エクスポート先 を指定します 55

56 4 今回は テキスト フゔ ルへデータを書き出すので 変換先 で フラット フゔ ル変換 先 を選択します フゔ ル名 へは エクスポート先となるフゔ ル名を入力しますが 今回は C:\従業員.tsv と入力します また 先頭データ行を列名として使用する チェ ック ボックスをチェックして フゔ ルの 行目には 列名が書き出されるようにします 設定後 次へ ボタンをクリックします 5. 次の テーブルのコピーまたはクエリの指定 画面では つ以上のテーブルまたはビュー からデータをコピーする が選択されていることを確認して 次へ ボタンをクリックしま す 56

57 6. 次の フラット フゔ ルの変換先の構成 画面では 転送元となるテーブルを選択します 今回は 従業員 テーブルを選択します また 列区切り記号 で タブ{t} を選択し タブ区切りのテキスト フゔ ルとしてエク スポートするようにします 続いて マッピングの編集 ボタンをクリックして 列マッピ ング ダ ゕログを表示します 無視 を選択することで データ転送の対象から外すこと ができる このダ ゕログでは エクスポートするデータの列名や データ転送の対象から外したい列の 変更などを行うことができます 今回は ローマ字姓 と ローマ字名 列をデータ転送の 対象から外すように 変換先 で 無視 を選択して OK ボタンをクリックします 7. フラット フゔ ルの変換先の構成 画面へ戻ったら 次へ ボタンをクリックします 57

58 8. 次の パッケージの保存および実行 では このデータ転送をすぐに実行するか 後から実行 するために保存するかを選択します 今回はすぐに実行するので すぐに実行する が選択されていることを確認して 次へ ボ タンをクリックします 9. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックするとデータのエクスポートが 開始されます 58

59 実行が完了すると 操作は正常に実行されました 画面が表示されます 状態 が 成功 と表示されていることを確認して 閉じる ボタンをクリックします エクスポートしたデータの確認 0. ウゖザードでエクスポートしたデータを確認するには エクスプローラから C:\従業員.tsv フゔ ルをダブル クリックして開きます 従業員.tsv フゔ ルを ダブル クリック タブ区切りでエクスポートされていることを確認できる また ローマ字姓 と ローマ字名 列は含まれていな いことも確認できる それぞれの列がタブ区切りで表示され ローマ字姓 と ローマ字名 列のデータはエクス ポートされていないことを確認できます 59

60 STEP. SSIS デザ ナの基本操作 この STEP では Integration Services SSIS の SSIS デザ ナ を使用して デ ータの変換を伴うデータの転送を行い SSIS デザ ナの基本操作を説明します この STEP では 次のことを学習します SSIS デザ ナの使用方法 データ フロー タスクによるデータの転送 派生列コンポーネントによるデータ変換 参照コンポーネントによる別テーブルのデータ取得 データ ビューゕによる転送中のデータの表示 60

61 . 作成するパッケージの概要 データ変換の概要 この STEP では SSIS デザ ナを利用して 次のようなデータ変換を行うパッケージ Excel 00 フゔ ルから SQL Server へのデータ転送時に変換処理を追加したパッケージ を作成します Excel フゔ ルは サンプル スクリプト内に Employee.xls という名前で置いてあります Employee.xls フゔ ル SQL Server 008 データ変換 姓 と 名 列 を文字列連結して 氏名 列を追加 性別コード の を 男性 を 女性 へ変換 部門番号 に 対応した 部門名 へ変換 作成するパッケージ.dtsx 作成するパッケージは 次のような構成になります Excel データ ソース 派生列コンポーネント データ フロー タスク 参照 コンポーネント SQL Server 変換先 実習を始める前に この STEP の実習を行うには STEP. の手順で Access データベースから SSISdb デ ータベース内へ ンポートして作成した 部門 テーブルが必要になります STEP. を実行し ていない場合は 事前に実行しておいてください 6

62 . データベースの作成 転送先となるデータベースの作成 この STEP では SSIS デザ ナを使用して Excel データを SQL Server 内へ取り込んでいく ので まずは 取り込み先 転送先 となる空のデータベースを作成します 次のように Management Studio で データベース フォルダを右クリックして 新しいデータベース をクリックし SSISdb という名前のデータベースを作成します データベース名に SSISdb と入力 データベース を右クリック して 新しいデータベース を クリック 6 [OK] ボタン をクリック

63 . SSIS デザ ナの起動 SSIS デザ ナの起動 次に パッケージの作成ツール SSIS デザ ナ を起動します. SSIS デザ ナを起動するには スタート メニューの すべてのプログラムから Microsoft SQL Server 008 を選択し SQL Server Business Intelligence Development Studio をクリックします. Business Intelligence Development Studio は Visual Studio 008 と統合されているの で Visual Studio 008 が起動します フゔ ル メニューの 新規作成 から プロジェクト をクリック 次に フゔ ル メニューから 新規作成 を選択して プロジェクト をクリックし 新 しいプロジェクトを作成します 6

64 . 新しいプロジェクト ダ ゕログが表示されたら プロジェクトの種類 で ビジネス ンテリジェンス プロジェクト を テンプレート で Integration Services プロジェ クト を選択します 4 プロジェクト名 プロジェクト名 へは SSISstep と入力し 場所 を C:\ へ 変更して OK ボタンをクリックします これにより Integration Services プロジェク トが作成され 次のような画面が表示されます ツール バー ツール ボックス ソリューション エクスプローラ SSIS デザ ナ プロパテゖ ウゖンドウ 64

65 Note ツールボックスの固定表示 Visual Studio 008 のデフォルトでは ツールボックスが隠れています SSIS デザ ナでは ツールボックス を頻繁に使用するので ツールボックスを固定して表示しておくと便利です ツールボックスを固定して表示する には 次のようにツールボックスをクリックしてツールボックスを表示し 自動的に隠す ボタンをクリックし ます 65 固定して表示される

66 .4 データ フロー タスクによるデータの転送 まずは データ変換を行わない 単純なデータ転送を行うパッケージを作成してみましょう データ転送は データ フロー タスク を利用して行うことができます データ フロー タスクの追加 4. データ フロー タスクを追加するには 次のように ツールボックス の 制御フロー項目 の中から データ フロー タスク を選択し SSIS デザ ナ上へドラッグ ゕンド ドロップ します SSIS デザ ナ上へ ドラッグ & ドロップ これにより データ フロー タスク が SSIS デザ ナ上に配置されます 5. 次に データ転送の詳細 転送元や転送先など を設定するために 次のように データ フ ロー タスク をダブル クリックします 66

67 ダブル クリック データフロー タブ が表示される ツールボックスがデータ 転送に関するコンポーネ ントの一覧に変更される これにより データ フロー タブが表示されて ツールボックス がデータ転送に関する コンポーネントの一覧へ変更されます 転送元データの指定 6. 転送元 Source データとして Excel フゔ ルを指定するには 次のように ツールボック ス の データ フローの変換元 カテゴリの中から Excel ソース を SSIS デザ ナ上 へドラッグ ゕンド ドロップします ドラッグ & ドロップ ダブルクリック 続いて 配置した Excel ソース をダブル クリックします 67

68 7. すると Excel ソース エデゖタ ダ ゕログが表示されるので 転送元の Excel フゔ ル を指定するために 新規作成 ボタンをクリックします これにより Excel 接続マネージャ ダ ゕログが表示されるので 次のように 参照 ボ タンをクリックます サンプル スクリプト内の Employee.xls を選択 フゔ ルを開く ダ ゕログでは サンプル スクリプト内の Employee.xls フゔ ル を選択して 開く ボタンをクリックします Excel 接続マネージャ ダ ゕログへ戻ったら 次のように Excel フゔ ル パス へ Employee.xls フゔ ルへのパスが表示され Excel バージョン が Microsoft Excel 68

69 97-00 となっていることを確認します Excel のバージョンを選択 Excel の最初の行を列名として扱う 4 また 先頭行に列名を含める チェック ボックスがチェックされていることも確認して OK ボタンをクリックします 8. Excel ソース エデゖタ ダ ゕログへ戻ったら 次のように Excel シートの名前 で 転送元となるデータが格納されている Excel のワーク シートを選択します ここで表示され るシート名は 実際のワーク シート名に $ マークをつけたものになります 転送されるデータの 内容を確認 4 5 今回のデータは Sheet へ入れているので Sheet$ を選択して プレビュー ボタ ンをクリックします これにより クエリ結果のプレビュー ダ ゕログが表示されて 転 送元のデータを確認することができます 確認後 閉じる ボタンをクリックして ダ ゕ ログを閉じます 転送先データベースの指定 9. 次に 転送先となる SQL Server 上のデータベースを指定するために 次のように ツール ボックス の データ フローの変換先 カテゴリから SQL Server 変換先 を SSIS デ ザ ナ上へドラッグ ゕンド ドロップします 69

70 0. 次に 転送元と転送先を関連付けるために SSIS デザ ナ上にある Excel ソース をクリ ックして 緑色の矢印 を表示し この矢印を SQL Server 変換先 まで ドラッグ ゕン ド ドロップして伸ばします クリックすると緑と赤 の矢印が表示される 緑色の矢印をドラッグ & ドロップして伸ばす Note 緑 は成功時 赤 は失敗 エラー 時の処理 緑の矢印は 成功時 の処理 赤の矢印は 失敗時 エラー発生時 の処理の流れを意味します エラー時の処 理については 本自習書シリーズの Integration Services 応用 で説明しています. 次に 転送先の詳細設定を行うために 次のように SQL Server 変換先 をダブルクリッ クします ダブル クリック 70

71 . すると SQL 変換先エデゖタ ダ ゕログが表示されるので 次のように 接続マネージャ ページで 新規作成 ボタンをクリックします OLE DB 接続マネージャの構成 ダ ゕログが表示されたら さらに 新規作成 ボタン をクリックします. 接続マネージャ ダ ゕログでは 次のように サーバー名 へ転送先となる SQL Server の名前を入力し データベースへの接続 で SSISdb を選択して OK ボタンをクリ ックします 7

72 4. OLE DB 接続マネージャの構成 ダ ゕログへ戻ったら データ接続 で サーバー 名.SSISdb が選択されていることを確認して OK ボタンをクリックします 5. SQL 変換先エデゖタ ダ ゕログへ戻ったら テーブルまたはビューを使用 で 新規作 成 ボタンをクリックします 7

73 SQL 文のテーブル名を 変更 これにより テーブルの作成 ダ ゕログが表示され 転送元のデータをもとに SQL Server 上へテーブルを作成できるようになります ここで表示される CREATE TABLE ステートメ ントのテーブル名を 社員 へ変更し OK ボタンをクリックします これにより 社員 テーブルを SSISdb データベース内へ作成することができます OK ボタンをクリック したときに実際にテーブルが作成されます 6. 続いて 次のように マッピング ページをクリックすると 転送元のデータの各列と 転送 先のデータの各列とのマッピング 対応 を設定することができます 7

74 使用できる入力列 と 使用できる変換先列 とで 同じ名前の列が割り当てられているこ とを確認して OK ボタンをクリックします データ転送の実行 7. ここまでの設定を確認するために 次のように デバッグ メニューから デバッグ開始 を クリックして データの転送を実行します 配置されているオブジェクトがすべて緑色に変わると データの転送が成功しています 8. 転送が成功したら デバッグ メニューから デバッグの停止 をクリックして デバッグ を終了します 74

75 転送されたデータの確認 9. 転送されたデータを確認するには Management Studio から 次のように SSISdb デ ータベースの テーブル フォルダを右クリックして 最新の情報に更新 最新の情報に更 新 をクリックします テーブルの一覧に 社員 という名前のテーブルが作成されていることを確認できます 続いて 社員 テーブルを右クリックして 上位 000 行の選択 をクリックします 75

76 表示された社員テーブルのデータが Excel フゔ ル内のデータ 以下 と同じであることを 確認します Employee.xls フゔ ル プロジェクトの保存 0. 次に ここまでの手順を保存するために Business Intelligence Development Studio から 次のように フゔ ル メニューの すべてを保存 をクリックして プロジェクトを保存し ます 76

77 .5 派生列コンポーネントによるデータ変換 次に 単純なデータ変換 コピー だけでなく データ変換を追加していきましょう ここでは 姓 と 名 列から 氏名 列へ変換し 性別コード 列からは を 男性 へ を 女性 へと変換するよ うな 性別 列を作成します このようなデータ変換は 派生列コンポーネントを利用して簡単に行うこと ができます 派生列コンポーネントの追加. まずは 前の手順で設定した Excel ソース から SQL Server 変換先 へ向かって出て いる 緑色の矢印 を右クリックして 削除 をクリックします これにより 矢印が削除 されます 右クリック. 次に データ変換を行うためのコンポーネントとなる 派生列 を Excel ソース と SQL Server 変換先 の間へ配置します このコンポーネントは ツールボックス の データ フ ロー変換 カテゴリにあります SSIS デザ ナ上へ ドラッグ ドロップ 77 ドラッグ ドロップ して少し下へ移動

78 . 次に 転送元と派生列を関連付けるために SSIS デザ ナ上にある Excel ソース をクリ ックして 緑色の矢印 を表示し この矢印を 派生列 まで ドラッグ ゕンド ドロップし て伸ばします ドラッグ ドロップ して伸ばす 4. 次に データ変換を定義するために 次のように 派生列 をダブル クリックします ダブル クリック すると 派生列変換エデゖタ ダ ゕログが表示されるので 列 フォルダを展開します これにより 転送元の列が一覧されます 5. 今回は 姓 列と 名 列をもとに 氏名 列を作成するので 次のように 派生列名 に 氏名 と入力して 派生列 が 新しい列として追加 となっていることを確認します 78

79 姓 列を 式 へ ドラッグ ドロップ 名 列をドラッグ & ドロップ 4 演算子を利用して 文字列連結をするように 式を入力して完成 次に 姓 と 名 列を文字列連結するために 式 へ 姓 と 名 列をドラッグ ゕン ド ドロップして配置し 次のように式を入力します 姓 + " " + 名 Note 文字列連結演算子の + Integration Services では 文字列の連結に + 演算子を利用し 文字列は " で囲みます したがって 上 のように式を記述すると 姓 列のデータに半角スペースを連結し さらに 名 列を連結できるようになりま す たとえば 姓が 山田 名が 太郎 なら 山田 太郎 のように連結できます このように 派生列コンポーネントを利用すると 既存の列データを元に 新しい列 元デー タから派生した列 を作成できるようになります 6. 続いて 性別コード 列をもとに 性別 列を作成するために 次のように 派生列名 に 性別 と入力して 派生列 が 新しい列として追加 となっていることを確認します 79

80 性別コード 列を ドラッグ ドロップ 式を入力して完成する 4 次に 性別コード が の場合は 男性 の場合は 女性 になるように 性別 列を作成するために 性別コード 列を 式 へドラッグ ゕンド ドロップして配置し 次 のように式を入力します 性別コード ==? "男性" : "女性" Integration Services では 条件分岐を行うための演算子として と を利用して 次のように利用します 条件式? 真の場合 : 偽の場合 この演算子は Access や Reporting Services などで利用できる IIF 関数と同じ効果が あります また 式で利用している == 演算子は 等価比較のための比較演算子です し たがって 上のように式を記述すると 性別コードが に等しい場合は 男性 そうでな い場合は 女性 という文字列へ変換するという意味になります 式を入力後 OK ボタンをクリックしてダ ゕログを閉じます 7. 次に 派生列で作成した 氏名 列と 性別 列が SQL Server へ転送されるようにします 次のように 派生列 をクリックして 緑色の矢印 を表示し この矢印を SQL Server 変 換先 までドラッグ ゕンド ドロップして伸ばします 80

81 ドラッグ ドロップ して伸ばす 転送先のテーブルの変更 8. 次に SQL Server 変換先 をダブル クリックします ダブル クリック 9. SQL 変換先エデゖタ ダ ゕログが表示されたら 接続マネージャ で サーバー名.SSISdb が選択されていることを確認します 続いて テーブルまたはビューを使用 で 新規作成 ボタンをクリックします 8

82 これにより テーブルの作成 ダ ゕログが表示されて 転送元のデータと派生列コンポー ネントをもとに CREATE TABLE ステートメントが自動生成されます 今回は この SQL ス テートメントを次のように変更します CREATE TABLE 社員 ( 社員番号 float, 氏名 nvarchar(5), 性別 nvarchar(), 部門番号 float ) テーブル名を 社員 へ変更 姓 列と 名 列 性別コード 列を 削除して 部門番号 列を一番下へ移動 その際に, カンマ の位置に注意 これにより 社員 テーブルを SSISdb データベース内へ作成することができます OK ボタンをクリックしたときに実際にテーブルが作成されます 0. 続いて 次のように マッピング ページをクリックすると 転送元のデータの各列と転送先 のデータの各列とのマッピング 対応 を設定することができます 氏名 列から 氏名 列へ 性別 列から 性別 列へ ドラッグ & ドロップ 4 前の手順で作成した 氏名 列がテーブルへ転送されるように 使用できる入力列 の 氏 名 列から 使用できる変換先列 の 氏名 列へドラッグ ゕンド ドロップします 同様に 性別 列に対しても同じ操作を行います これにより 使用できる入力列 から 使用できる変換先列 へ 本の線 マッピング が 8

83 追加され 作成した 氏名 列と 性別 列を 社員 テーブルの 氏名 列と 性別 列へそれぞれ割り当てることができます データ転送の実行. ここまでの設定を確認するために 次のように デバッグ メニューから デバッグ開始 を クリックして データ転送を実行します 配置されているオブジェクトがすべて緑色に変わると データの転送が成功しています. 転送が成功したら デバッグ メニューから デバッグの停止 をクリックして デバッグ を終了します 転送されたデータの確認. 転送されたデータを確認するには Management Studio で 次のように SSISdb デー タベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックします 8

84 テーブルの一覧に 社員 という名前のテーブルが作成されていることを確認できます 4. 続いて 社員 テーブルを右クリックして 上位 000 行の選択 をクリックします 4 姓 と 名 が 連結されている 5 男性と女性に変換さ れている 表示されたデータが Excel フゔ ル内のデータ 以下 をもとに変換され 姓 と 名 列を連結して 氏名 列が 性別コード 列が変換されて 性別 列が追加されていること を確認できます Employee.xls フゔ ル 84

85 プロジェクトの保存 5. 次に ここまでの手順を保存するために Business Intelligence Development Studio から 次のように フゔ ル メニューの すべてを保存 をクリックして プロジェクトを保存し ます 85

86 .6 参照コンポーネントによる別テーブルのデータ取得 次に 社員データの 部門番号 から 部門 テーブルにある 部門名 を取得 参照 してデータを転送 するようにパッケージを変更します 他のテーブルのデータを参照するには 参照 Lookup コンポー ネントを利用します SSISdb データベース内 の 部門 テーブル Employee.xls フゔ ル 参照 結果 参照コンポーネントの追加 6. まずは 前の手順で設定した 派生列 から SQL Server 変換先 へ向かって出ている 緑 色の矢印 を右クリックして 削除 をクリックします これにより矢印が削除されます 右クリック 86

87 7. 次に ツールボックス の データ フロー 変換 カテゴリの中から 参照 コンポーネン トを SSIS デザ ナ上へドラッグ ゕンド ドロップします 8. 次に SSIS デザ ナ上にある 派生列 コンポーネントをクリックして 緑色の矢印 を表 示し この矢印を 参照 コンポーネントまで ドラッグ ゕンド ドロップして伸ばします 緑色の矢印をドラッグ & ドロップして伸ばす 9. 次に 取得するデータを指定するために 参照 コンポーネントをダブル クリックします 87

88 ダブル クリック 40. すると 参照変換エデゖタ ダ ゕログが表示されるので 接続 ページをクリックして開 きます 4. 接続 ページが表示されたら 次のように OLE DB 接続マネージャ で 新規作成 ボ タンをクリックします 88

89 OLE DB 接続マネージャの構成 ダ ゕログが表示されたら 次のように STEP で作 成した SSISdb データベースへ接続するために 新規作成 ボタンをクリックします 4. 接続マネージャ ダ ゕログでは 次のように サーバー名 へ転送先となる SQL Server の名前を入力し データベースへの接続 で SSISdb を選択して OK ボタンをクリ ックします 89

90 4. OLE DB 接続マネージャの構成 ダ ゕログへ戻ったら データ接続 で サーバー 名.SSISdb が選択されていることを確認して OK ボタンをクリックします 44. 参照変換エデゖタ ダ ゕログへ戻ったら テーブルまたはビューを使用する で参照先 のテーブルを指定します 今回は 部門 テーブルのデータを参照するので 次のように dbo.部門 テーブルを選択します 90

91 4 部門テーブルのデータを確認するために プレビュー ボタンをクリックします 確認後 閉 じる ボタンをクリックします 45. 続いて 列 ページをクリックして 参照する列を設定します 次のように 使用できる入 力列 の 部門番号 列の参照先が 使用できる参照列 の 部門番号 列となるように ドラッグ ゕンド ドロップします 部門番号 列を ドラッグ ドロップ チェックボックスではなく [名前] 列内の 部門番号 へドラッグ ド ロップすることに注意 チェック ボックスへドラッグ ドロップした 場合は 何も起こらない しかし データ型が浮動小数点 float 型のため 参照できない という主旨のエラーメッ セージが表示され 正しく設定することができません 参照コンポーネントは データ型が一 致しないと参照することができないためです 転送元の 部門番号 列は float 型で 参照 先の 部門 テーブルの 部門番号 列は int 型で データ型が一致していません そこで 9

92 ここでは ひとまず OK ボタンをクリックして エラー メッセージを閉じ 接続 ペー ジへ戻ります 続いて OK ボタンをクリックして 参照変換エデゖタ ダ ゕログを閉じます 46. 転送元の 部門番号 列のデータ型を float 型から int 型へ変更するには 次のように Excel ソース を右クリックして 詳細エデゖタの表示 をクリックします 右クリック 47. これにより Excel ソースの詳細エデゖタ ダ ゕログが表示されるので 次のように 入 力プロパテゖと出力プロパテゖ タブをクリックします 9

93 4 5 クリックして選択 6 このダ ゕログでは Excel ソースの出力 の 出力列 を展開して Excel フゔ ルから 転送される列を一覧します この一覧から 部門番号 列を選択して 表示されるプロパテゖ の中から Data Type データ型 を探します この値は 倍精度浮動小数点数 SQL Server での float 型に相当するデータ型 へ設定されているので 4 バ ト符号付き整数 SQL Server での int 型に相当するデータ型 へ変更します データ型の変更後 OK ボタンをクリックして ダ ゕログを閉じます 48. 次に 先ほど失敗した参照列の設定を再度行うために 参照 コンポーネントをダブル クリ ックします 9 ダブル クリック

94 49. 参照変換エデゖタ ダ ゕログが表示されたら 列 ページをクリックして開きます 部門番号 列を ドラッグ & ドロップ チェック 4 使用できる入力列 の 部門番号 列を 使用できる参照列 の 部門番号 列へドラッグ ゕンド ドロップすると 今度はエラーメッセージが表示されずに線が追加されます 続いて 取得したい列を指定するために 使用できる参照列 の 部門名 列のチェック ボックスを チェックし OK ボタンをクリックします これで部門テーブルから部門名を参照 取得 できるようになります 50. 次に 取得した部門名が SQL Server へ転送されるように 参照 コンポーネントの緑色の 矢印を SQL Server 変換先 までドラッグ ゕンド ドロップして伸ばします すると 入出力の選択 ダ ゕログが表示されるので 出力 で 参照の一致出力 を選択 して OK ボタンをクリックします これで 参照 コンポーネントで参照した値 参照 元の部門番号と参照先の部門番号が一致する部門名 を SQL Server 変換先 へ渡せるよう になります 94

95 転送先のテーブルの変更 5. 次に 転送先のテーブルを変更するために SQL Server 変換先 をダブル クリックして SQL 変換先エデゖタ ダ ゕログを開きます ダブル クリック 5. 接続マネージャ で サーバー名.SSISdb が選択されていることを確認し テーブル またはビューを使用 で 新規作成 ボタンをクリックします これにより テーブルの作成 ダ ゕログが表示されて 転送元のデータと派生列 参照コ ンポーネントで参照した列をもとに CREATE TABLE ステートメントが自動生成されます 今回は このステートメントを次のように変更します テーブル名を 社員 へ変更 姓 列と 名 列 性別コード 列 部門番号 列を削除する 95

96 ステートメント変更後 OK ボタンをクリックすると 社員 テーブルが作成されます 5. SQL 変換先エデゖタ ダ ゕログに戻ったら 次のように マッピング ページをクリッ クします 部門名 列から 武門名 列 へドラッグ & ドロップ 4 参照 コンポーネントで参照した 部門名 列がテーブルへ転送されるように 使用でき る入力列 の 部門名 列から 使用できる変換先列 の 部門名 列へドラッグ ゕンド ド ロップします 線が追加されたことを確認して OK ボタンをクリックします データ転送の実行 54. ここまでの設定を確認するために デバッグ メニューから デバッグ開始 をクリックし て パッケージを実行します 配置されているオブジェクトがすべて緑色に変わると データの転送が成功しています 55. 転送が成功したら デバッグ メニューから デバッグの停止 をクリックして デバッグ を終了します 96

97 転送されたデータの確認 56. 転送されたデータを確認するには Management Studio から 次のように SSISdb デ ータベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックしま す テーブルの一覧に 社員 という名前のテーブルが作成されていることを確認できます 続いて 社員 テーブルを右クリックして 上位 000 行の選択 をクリックします 97

98 4 部門番号 列のかわりに 部門名 列が追加 部門番号 列の代わりに 部門名 列が追加されていることを確認できます このように参 照コンポーネントを利用すると 他のテーブルを参照して 関連データを取得できるようにな るので 大変便利です プロジェクトの保存 57. 最後に ここまでの手順を保存するために Business Intelligence Development Studio か ら 次のように フゔ ル メニューの すべてを保存 をクリックして プロジェクトを保 存します 98

99 .7 データ ビューゕによる転送中のデータの表示 データの転送は ここまで作成してきたように 矢印の順にそれぞれのコンポーネントを通って行われます が データ ビューゕ機能を利用すると 次のようにコンポーネント間を転送されるデータを確認できるよう になります データ ビューゕを利用すると 変換 転送中のデータを確認 できるので 正しく実行され ているかを容易に確認できる データ ビューゕは 派生列や参照コンポーネントによるデータ変換処理が正しく行われているかどうかの確 認目的 デバッグ目的 として利用できるので 大変便利です ここでは これまで作成してきた SSISstep プロジェクトのパッケージを引き続き利用して データ ビューゕの機能を試してみましょう データ ビューゕの設定 58. 最初に Excel ソース から 派生列 へ転送されるデータを確認するためのデータ ビュー ゕを設定してみましょう 次のように SSIS デザ ナ上の Excel ソース と 派生列 の 間の矢印を右クリックして データ ビューゕ をクリックします 右クリック 99

100 59. すると 次のように データ フロー パス エデゖタ ダ ゕログが表示されるので デー タ ビューゕ ページで 追加 ボタンをクリックします 4 これにより データ ビューゕの構成 ダ ゕログが表示されるので グリッド を選択し て OK ボタンをクリックします データ フロー パス エデゖタ ダ ゕログへ戻ると 設定したデータ ビューゕが表示され ていることを確認できます 確認後 OK ボタンをクリックします 60. デザ ナへ戻ると 次のようなメガネ マークのデータ ビューゕ ゕ コンが追加されている ことを確認できます 00

101 メガネ マークの データ ビューゕ ゕ コン これでデータ ビューゕの設定が完了です 6. 同様にして 派生列 と 参照 コンポーネントとの間にもデータ ビューゕを設定しましょ う 次のように SSIS デザ ナ上の 派生列 と 参照 の間の矢印を右クリックして デ ータ ビューゕ をクリックします 右クリック 6. データ フロー パス エデゖタ ダ ゕログが表示されたら 追加 ボタンをクリックしま す 4 5 0

102 データ ビューゕの構成 ダ ゕログでは グリッド を選択して OK ボタンをクリッ クします データ フロー パス エデゖタ ダ ゕログへ戻ったら OK ボタンをクリック します 6. 次のようにデータ ビューゕ ゕ コンが追加されることを確認します 実行してデータ ビューゕの効果を確認 64. 次に デバッグ メニューから デバッグ開始 をクリックして パッケージを実行します 65. すると 派生列 コンポーネントが黄色になってデータ転送がここで一時的に停止し デー タ ビューゕ ウゖンドウが表示されます 0

103 データ ビューゕでは Excel ソース から 派生列 に転送されてきたデータを確認するこ とができます このデータは 元の Excel データと同じであることを確認し ボタンをクリックし て 転送を再開します 66. 次に 参照 コンポーネントが黄色になってデータ転送がここで再び一時的に停止し 次の ように データ ビューゕ ウゖンドウが表示されます データ ビューゕでは 派生列 から 参照 コンポーネントへ転送されてきたデータを確認 することができます 派生列 新しい列 として作成した 氏名 列と 性別 列が追加されていることを確認して ボタンをクリックし 転送を再開します 67. すべてのコンポーネントが緑色に変わると データの転送が完了します 転送完了後 デバ ッグ メニューから デバッグの停止 をクリックして デバッグを終了します 0

104 データ ビューゕの削除 68. 設定したデータ ビューゕを削除したい場合は 次のように線の矢印を右クリックして デー タ ビューゕ をクリックします 右クリック 4 5 データ フロー パス エデゖタ が表示されたら データ ビューゕ に一覧されているデ ータ ビューゕを選択して 削除 ボタンをクリックします 69. もう一つのデータ ビューゕに対しても 同様の操作をして 削除することができます 右クリック

105 プロジェクトの保存 70. 次に ここまでの設定を保存するために フゔ ル メニューの すべてを保存 をクリッ クして プロジェクトを保存します 05

106 STEP 4. パッケージの実行 この STEP では 保存したパッケージを実行するためのユーテゖリテゖとなる dtexecui.exe と dtexec.exe や パッケージを定期的に実行する方法を説明し ます この STEP では 次のことを学習します パッケージの実行 (dtexecui.exe) コマンドプロンプトからの実行 (dtexec.exe) パッケージの定期実行 (SQL Server Agent ジョブへの登録 ) 06

107 4. パッケージの実行 dtexecui SSIS パッケージを実行するには dtexecui.exe パッケージ実行ユーテゖリテゖ を使用します ここで は STEP で作成したパッケージ SSISstep プロジェクト内にあるパッケージ を実行してみましょ う パッケージの確認. まずは STEP で作成したパッケージ フゔ ルを確認します Windows エクスプローラを 起動して C:\SSISstep\SSISstep\bin フォルダを展開します この中にある Package.dtsx というフゔ ルが SSISstep プロジェクトで作成したパッケージ フゔ ルです パッケージ フゔ ル dtexecui の起動. エクスプローラで パッケージ フゔ ル Package.dtsx をダブル クリックします ダブル クリック. すると dtexecui.exe が起動されて 次のような パッケージ実行ユーテゖリテゖ ダ ゕログが表示されます 07

108 パッケージ ソース が フゔ ル システム になっていて パッケージ がパッケージ フ ゔ ルのパス 今回は C:\SSISstep\SSISstep\bin\Package.dtsx になっていること を確認します 次に 実行 ボタンをクリックして パッケージの実行を開始します 4. パッケージの実行が開始されると 次のように パッケージ実行の進行状況 ダ ゕログが 表示され 進捗状況が表示されます 完了後 閉じる ボタンをクリックします 08

109 Note 実行時に接続先を変更することも可能 パッケージ実行ユーテゖリテゖ の 接続マネージャ ページを利用すると パッケージの実行時に接続先 SQL Server の名前やデータベース名 Excel フゔ ルの格納されているフォルダ パスなど を変更することも可能で す 変更したい接続先を チェックする 接続先のサーバー名 Data Source やデータベース名 Initial Catalog 認証方法などを直接変更できる この機能は 開発機と本番機で SQL Server の名前が異なる場合や データベース名が異なる場合に大変便利です また dtexecui では 接続先だけでなく パッケージ内に配置したコンポーネントのその他のプロパテゖについて も可変にして 実行時に値を変更することができます これは 構成フゔ ル.dtsConfig を利用することで行え ますが これについては 本自習書シリーズの Integration Services 応用 で説明します 09

110 4. コマンド プロンプトからのパッケージ実行 dtexec SSIS パッケージは dtexec.exe を利用すると コマンドから実行することもできます dtexec の利用. dtexec を利用して STEP で作成したパッケージを実行するには コマンド プロンプトか ら次のようにコマンドを入力し Enter キーを押して実行します dtexec /FILE "C:\SSISstep\SSISstep\bin\Package.dtsx" すると パッケージの進捗状況が表示され パッケージの実行が完了すると 次のようなメッ セージ表示されます Note dtexecui を利用したコマンド ラ ン オプションの生成 dtexecui.exe の コマンド ラ ン ページでは GUI ベースで設定した実行時のオプションに対して コマン ド プロンプト dtexec から実行する際のコマンド ラ ン オプションを生成してくれる機能があるので これ をコピーして dtexec を実行すると便利です dtexec コマンド用の コマンド ラ ン オプ ションが表示される コピーして利用できる 0

111 4. パッケージの定期実行 SQL Server Agent ジョブ SSIS パッケージを定期的に実行したい場合は SQL Server Agent サービスの ジョブ 機能を利用しま す ここでは STEP で作成したパッケージ C:\SSISstep\SSISstep\bin\Package.dtsx を定期 的に実行する手順を例に説明します SQL Server Agent サービスの起動 パッケージをジョブとして定期的に実行させるには 事前に SQL Server Agent サービスを開 始しておく必要があります. SQL Server Agent サービスを開始するには 次のように Management Studio のオブ ジェクト エクスプローラで SQL Server エージェント を右クリックして 開始 をク リックします 右クリック 4 すると SQLSERVERAGENT サービスを開始しますか という主旨のメッセージが表示 されるので はい ボタンをクリックします SQL Server Agent のジョブとしての登録. パッケージを SQL Server Agent のジョブとして登録するには 次のようにオブジェクト エ クスプローラで SQL Server エージェント の ジョブ フォルダを右クリックして 新 しいジョブ をクリックします

112 これにより 新しいジョブ ダ ゕログが表示されるので 名前 へ任意の名前 パッケー ジの実行など を入力します. 次に ステップ ページをクリックして表示し 新規作成 ボタンをクリックします これにより 新しいジョブ ステップ ダ ゕログが表示されるので ステップ名 へ任意 の名前 STEP パッケージ実行など と入力します パッケージをステップとして登録するには 種類 で SQL Server Integration Services パッケージ を選択します また パッケージ ソース で フゔ ル システム を選択し パ ッ ケ ー ジ へ STEP で 作 成 し た パ ッ ケ ー ジ フ ゔ ル C:\SSISstep\SSISstep\bin\Package.dtsx を選択して OK ボタンをクリックし ます

113 4. 次に ジョブのスケジュールを設定するために 次のように スケジュール ページをクリッ クして開きます 新規のスケジュールを作成するために 新規作成 ボタンをクリックします 5. すると 新しいジョブ スケジュール ダ ゕログが表示されて スケジュールの設定が行え ます スケジュール名 単位 日 週 月 の指定 何週ごとか 何曜日に 実行するか 週を指定 した場合 4 何時に実行するか または 何時間おきに実行するか 5 名前 へ任意のスケジュール名を入力し どのくらいの頻度で実行するかや 実行日時など

114 を設定します 例えば 毎日夜 :00 に実行したい場合は 次のように設定します 毎日 夜の :00 に実行したい場合は 実行 を 毎日 間隔 を １ 日 回 を :00:00 に設定する 6. 最後に 新しいジョブ ダ ゕログで OK ボタンをクリックすれば ジョブの登録が完了 です 以上で 設定したスケジュールの時刻にパッケージが自動実行されるようになります 4

115 ジョブの確認 手動実行 次に 登録したパッケージのジョブが正常に実行されるかどうかを確認するために 手動で実行し てみましょう 登録したジョブを手動で実行するには 次のように ジョブ フォルダ内の登録したジョブ パ ッケージの実行 を右クリックして ステップでジョブを開始 をクリックします 7. 4 ジョブが開始され ジョブの開始 ダ ゕログが表示されます すべての 状態 が 成功 になれば ジョブの実行 パッケージの実行 が成功しています 結果を確認後 閉じる ボタンをクリックします 5

116 4.4 Integration Services とデータ ウェゕハウスの関係 ETL ここでは Integration Services とデータ ウェゕハウスとの関係について説明します Integration Services は データ ウェゕハウスを構築する上で欠かせないツールとなります 一般的なデータ ウェゕハウスの構成は 次のようになります 広義のデータ ウェゕハウス データ マート 基幹系システム RDB 専用 DB セントラル データ ウェゕハウス SQL Server OLAP ツール クラ ゕント データ分析 予測 グラフ クロス集計レポー ト 経営ダッシュボード データ マ ニング RDB 専用DB OLAP サーバー キューブ 多次元DB メ ン フレーム 汎 用機 や UNIX など RDB 専用 DB 狭義のデータ ウェゕハウス データ分析 意 思決定のための 全社統合データ その他のデータ いろいろな場所へ 散在するデータ フォーマットの不 統一など キューブ 部門や地域 使 用目的ごとに抜 き出したデータ ETL ツール さまざまなデータソースか らデータ抽出 変換 転送 BI Business Intelligence ビジネス ンテリジェンス システム データ ウェゕハウス DWH : Data Warehouse は 基幹業務系のシステムのデータを整理 統合 蓄積 したもので Warehouse は 倉庫 という意味です 蓄積したデータに対してさまざまな分析を行うこと で 売上や利益を上げるための方針を立案したり 競合他社に対する施策を練ったりするために利用します Integration Servcies は ETL ツール データ ウェゕハウスを構築する上で欠かせないツールとなるのが Integration Services のよう に さまざまなデータ ソースからデータを抽出して フォーマットの統一 データ変換 加工 が行え 分析へ適した形へ変換 転送できるツールです このようなツールは 抽出 転送 ロー ドが行えることから ETL Extraction Transformation and Loading ツールと呼ばれていま す このため Integration Services は ETL ツールとも呼ばれます データ ウェゕハウスの構築作業のうち ETL ツールによる作業が 6 割 8 割を占めるとも言わ れており 非常に重要な役割を担っています Note データ ウェゕハウスや BI の定義は 人によってさまざま データ ウェゕハウスの定義は 使う人によってさまざまで 狭い意味では 企業内に散在する業務システムのデータを 6

117 抽出 変換 統合して蓄積したデータの集まり 全社統合データ を指し セントラル データ ウェゕハウス と呼ば れます これに対して 広い意味では データの蓄積から活用までを含めた意思決定を支援するシステム全体 を指しま す データマートや OLAP サーバーを含めて あるいは人によっては OLAP ツールまでを含めてデータ ウェゕハウス と呼ぶこともあります 最近は OLAP ツールを利用した高度なデータ分析までを含めたシステム全体 を指して BI Business Intelligence ビジネス ンテリジェンス システムと呼ぶことが多くなり データ ウェゕハウスという言葉は セントラル データ ウ ェゕハウスやデータマートを指すことが多くなっています なお 狭義での BI は OLAP サーバーや OLAP ツールを 指します データ ウェゕハウスと SQL Server の関係 SQL Server には Database Engine や Integration Services だけでなく Reporting Services レポート サーバー や Analysis Services 分析サービス など いろいろな機能が提供され ています これらの SQL Server のサービスと 一般的なデータ ウェゕハウスの構成を当てはめ ると 次のようになります 広義のデータ ウェゕハウス 基幹系システム データマート OLAP ツール クラ ゕント データ分析 予測 グラフ クロス集計レポー ト 経営ダッシュボード データ マ ニング セントラル データ ウェゕハウス Microsoft Office Excel SQL Server SQL Server Database Engine Database Engine データベース エンジン データベース エンジン Internet Explorer Web ブラウザ OLAP サーバー SQL Server Database Engine データベース エンジン メ ン フレーム 汎 用機 や UNIX など 狭義のデータ ウェゕハウス SQL Server Analysis Services 分析サーバー その他のデータ ETL ツール SQL Server SQL Server Reporting Services レポート サーバー Integration Services データ転送 変換 BI Business Intelligence ビジネス ンテリジェンス システム SQL Server の Database Engine Services RDB Engine リレーショナル データベース エン ジン は 基幹系システムやセントラル データウェゕハウス データマートにおけるデータの格 納先として 十二分に利用することができます 実際 SQL Server を利用したテラ バ ト TB サ ズのデータ ウェゕハウスを構築している企業は多数あり 筆者もそういった企業のコンサル テゖング 物理設計や論理設計 BI システム設計など を行った経験があります また SQL Server 008 では データ ウェゕハウス関連の機能 特にパフォーマンス が大き く向上しており より大規模かつミッション クリテゖカルなデータベース環境への対応を強化し ています 7

118 4.5 SQL Server 008 のデータ ウェゕハウス関連のパフォーマンス向上 SQL Server 008 の新機能 データ ウェゕハウス関連 SQL Server 008 では データ ウェゕハウス関連のパフォーマンスが大きく向上しています そ の主なものは 次のとおりです スター ジョ ンのパフォーマンス向上 パーテゖションのパラレル処理のパフォーマンス向上 つのパーテゖションを複数スレ ッドが処理可能に パーテゖション単位のロックが可能に またロック エスカレーションの禁止も可能に データ圧縮 Data Compression 機能によるパフォーマンス向上とデゖスク コストの削 減 リソース ガバナーによるリソース調整 ワークロード単位での CPU 利用率やメモリ使用 量の調整が可能に バックゕップ圧縮によるバックゕップ リストゕのパフォーマンス向上とデゖスク コスト の削減 ンデックス付きビューがパーテゖションへ割り当て可能に 実行プランの固定 プラン キャッシュをもとに実行プランの固定が可能に 従来のバージ ョンでは XML 実行プランをもとに固定が可能 Integration Services のパフォーマンス向上 SQL Server 008 では Integration Services のパフォーマンスも向上しています 具体的には 次のとおりです 内部処理エンジンの改良により データ フロー タスクのパフォーマンスが向上 Lookup 参照 コンポーネントのパフォーマンス キャッシュ機能 が向上 8

119 4.6 BI 系のサービス Reporting Services Analysis Services 前述の図 データ ウェゕハウスと SQL Server の関係 のように SQL Server には Reporting Services レポート サーバー や Analysis Services 分析サービス など データベース エンジンのほかにも いろいろな機能が提供されています これらは データ分析のための BI Business Intelligence ビジ ネス ンテリジェンス システムを構築するうえでは 非常に重要なツールとなっています Reporting Services レポート サーバー SQL Server 008 Reporting Services SSRS は レポートの 作成 や 管理 配布 が 行える レポーテゖング ツール です 会社の売上レポートやグラフ クロス集計 ABC 分析 パ レート図 帳票 見積書や納品書 請求書など を GUI ベースで容易に作成し レポート サー バーへ配置して Web ブラウザから閲覧することができる機能です Reporting Services を利用すると 次のようなレポートを簡単に作成することができます ABC 分析グラフ パレート図 Y軸対応 D 棒グラフ クロス集計レポート 円グラフ これらのレポートを作成する手順は 本自習書シリーズの Reporting Services 入門 で説明し ているので ぜひご覧になってみてください SQL Server 008 自習書シリーズ Reporting Services 入門 のダウンロードはこちら Analysis Services 分析サービス SQL Server 008 Analysis Services SSAS 分析サービス は "データ分析" のためのサーバ 9

120 ー機能です Microsoft Office Excel や Microsoft Office PeformancePoint Server 007 などをクラゕントツールとして利用することで 企業の売上分析やクロス集計レポート ABC 分析 ( パレート図 ) 財務諸表分析 経営ダッシュボード データマニングなどを容易に行えるようになります Analysis Services を利用すると 次のようなデータ分析を簡単に行えるようになります 商品分類ごとの商品売上トップ 5 都道府県ごとの商品売上の割合商品区分ごとの商品売上金額商品ごとの売上金額 ( 飲料区分 ) 区分ごとの商品売上の推移 ( 四半期単位 ) クロス集計 ABC 分析レポート Analysis Services によるデータ分析を行う手順は 本自習書シリーズの Analysis Services 入 門 で説明しています この自習書は こちらからダウンロードすることができます 0

121 4.7 SQL Server 008 Integration Services の新機能 SQL Server 008 Integration Services の新機能 SQL Server 008 Integration Services は 以前のバージョンから大きな変更点はありませんが 前述したように内部的な処理エンジンの改良によるパフォーマンスの向上や C# でスクリプトを 記述できるようになった点など 細かいところで機能が向上しています SQL Server 008 での主な新機能は 次のとおりです 内部処理エンジンの改良により データ フロー タスクのパフォーマンスが向上した Lookup 参照 コンポーネントのパフォーマンス キャッシュ機能 が向上した データ プロフゔ ル タスク と Data Profiler Viewer ツールが追加され データ の分布情報を容易に確認できるようになった スクリプト環境が Microsoft Visual Studio for Applications (VSA) から Microsoft Visual Studio 005 Tools for Applications (VSTA) に変わり C# によるスクリプトの 記述や Web 参照の記述ができるようになった データ フロー タスクの変換元で DataReader ソース に代わって ADO NET Source が追加され データ フロー タスクの変換先でも ADO NET Destination が追加された なお データ プロフゔ ル タスク と Data Profiler Viewer ツールについては 本自習書シ リーズの SQL Server 008 の注目の新機能を チ早く試してみよう で扱っていますので そちらを参考にしてください SQL Server 008 自習書シリーズ 注目の新機能を チ早く試してみよう のダウンロードは こちら

122 4.8 おわりに おわりに 最後まで試された皆さん いかがでしたでしょうか SQL Server 008 Integration Services は 業務で大変役立つツールですので ぜひ活用してみていただければと思います 今回は 入門編 ということで 基本操作のみをご紹介しましたが Integration Services では 条件分割や文字 マップ ログ記録 ベント ハンドラ チェックポ ント ADO.NET Source と ADO.NET Destination スクリプト コンポーネントによる複雑な変換処理とグローバル変数の利用 Integration Services サービスへの接続など まだまだたくさんの機能が提供されています こ れらの応用的な利用方法については 本自習書シリーズの Integration Services 応用 で説明 しています Integration Services の 徹底パフォーマンス検証 ホワ トペーパーのご紹介 SQL Server 005 での情報になりますが マ クロソフトでは Integration Services のパフォ ーマンスを検証した結果をまとめたものをホワ トペーパー 技術文書 として公開しています この文書では 異なるハードウェゕ環境 (-bit 64-bit 環境) でのパフォーマンス比較や 並列 度の変更したことによる効果 挿入コミットサ ズを変更した場合の変化 複雑な変換処理を伴う 場合の 64-bit 化による効果などが記載されています SQL Server 008 の Integration Services を利用する場合にも 有益な情報がたくさん記載されていますので ぜひご覧になってみてくださ い SQL Server 005 徹底検証シリーズのダウンロードはこちら SQL Server 008 共同検証プロジェクト 徹底検証ホワ ト ペーパー 徹底検証ホワ ト ペーパーは SQL Server 008 早期実証プロジェクト CQI Center of Quality Innovation の成果物です CQI は マ クロソフトと日本電気株式会社 日本ヒュー レット パッカード株式会社 日本ユニシス株式会社の 社と共同で実施し 実際のユーザーに 対する SI System Integration プロジェクトを想定した RFP Request for Proposal 提 案依頼書 に基づいた 機能動作検証 や パフォーマンス検証 を行ったプロジェクトです このプロジェクトでは 次の 4 つのシナリオを実施しています. コンプラ ゕンス シナリオ マ クロソフトによる検証プロジェクト 内部統制や日本版 SOX 法 情報漏えい対策 で求められるコンプラ ゕンスに対応するためのガ ドラ ン提示を目的とし 日本版 SOX 法への対応に必要となる機能の実装方法の確立やシステムに与える影響の計測など を実施

123 . 移行 / ゕップグレード シナリオ 日本ユニシス株式会社様との共同検証プロジェクト SQL Server 000 および 005 か ら SQL Server 008 への移行や互換性に関する検証を実施. サーバー統合シナリオ 日本ヒューレット パッカード株式会社様との共同検証プロジェクト 複数の SQL Server を 台の 64 ビット サーバーに集約するメリット 運用管理方法などに関する検証を実 施 4. DWH データ ウェゕハウス シナリオ 日本電気株式会社様との共同検証プロジェクト SQL Server 008 のデータ ウェゕハ ウス環境における実装方法の検証を実施 これらの検証結果 ホワ ト ペーパー は 次の URL からダウンロードすることができます SQL Server 徹底検証シリーズ この自習書シリーズの次のステップのハ レベルな情報として ぜひご覧いただければと思いま す

124 執筆者プロフゖール 有限会社エスキューエル クオリテゖ SQL Server と.NET を中心とした コンサルテゖング サービス と メンタリング サービス を提供 主なコンサルテゖング実績 9 TB データベースの物理 論理設計支援 パーテゖショニング対応など 秒あたり,000 Batch Request の ASP ゕプリケーション サービス プロバ ダ サ トの チューニング ピーク時の CPU 利用率 00% を 0% まで軽減 高負荷テスト ラッシュテスト 実施のためのテスト ゕプリの作成支援 大手流通系システムの夜間バッチ実行時間を 4 時間から 時間半へ短縮 大手 ンターネット通販システムの夜間バッチ実行時間を 5 時間から 時間半へ短縮 宅配便トラッキング情報の日中バッチ実行時間を 時間から 5 分へ短縮 検索系 Web サ トのチューニング 0 倍以上のパフォーマンス UP を実現 0 Server によるレプリケーション環境のチューニング TB のセキュリテゖ監査ゕプリケーションのチューニング 大手家電メーカーの制御系ゕプリケーション 00GB のチューニングと運用管理設計 約,000 本のストゕド プロシージャとユーザー定義関数のチューニング ASP.NET / ASP Active Server Pages ゕプリケーションのチューニング 大手ゕミューズメント企業の BI システム設計支援 外資系医療メーカーの Analysis Services による 販売分析 システムの設計支援 大手企業の Analysis Services による 財務諸表分析 システムの設計支援 Analysis Services OLAP キューブのパフォーマンス チューニング etc 松本美穂 まつもと みほ 有限会社エスキューエル クオリテゖ 代表取締役 Microsoft MVP for SQL Server / PASSJ 理事 MCDBA Microsoft Certified Database Administrator MCSD for.net Microsoft Certified Solution Developer 現在 SQL Server を中心とするコンサルテゖング 企業に対するメンタリング サービスなどを行っている 今までに手 がけたコンサルテゖング案件は テラバ ト クラスの DB から少人数向け小規模 DB までと 幅広く多岐に渡る 得意分 野はパフォーマンス チューニング コンサルテゖング業務の傍ら 講演や執筆も行い Microsoft 主催の最大 ベント Tech Ed や PASSJ が主催するカンフゔレンスなどでスピーカーとしても活躍中 著書の SQL Server 000 でいってみよう と ASP.NET でいってみよう いずれも翔泳社刊 はトップ セラー 前者は 8,500 部 後者は 5,500 部発行 の びのびになっている SQL Server の新書籍は もうじき刊行予定 松本崇博 まつもと たかひろ 有限会社エスキューエル クオリテゖ 取締役 Microsoft MVP for SQL Server / PASSJ 理事 MCDBA Microsoft Certified Database Administrator MCSD for.net Microsoft Certified Solution Developer SQL Server のパフォーマンス チューニングを得意とするコンサルタント 過去には 約,000 本のストゕド プロシー ジャのチューニングや テラバ ト級データベースの論理 物理設計 運用管理設計 高可用性設計などを行う また 過 去には 実際のゕプリケーション開発経験 ASP ASP.NET VB 6.0 Java Access VBA など と システム管理者 IT Pro 経験もあり SQL Server だけでなく ゕプリケーションや OS Web サーバーを絡めた 総合的なコンサルテゖン グが行えるのが強み 最近は Analysis Services と Excel 007 による BI ビジネス ンテリジェンス システムも 得意とする 執筆時のペンネームは 百田昌馬 月刊 Windows Developer マガジンの SQL Server でど んといっ てみよう DB マガジンの SQL Server トラの穴 を連載 マ クロソフト公開のホワ トペーパー 技術文書 の ゴースト ラ ターとして活動することもあり 過去 マ クロソフト認定トレーナー時代には SMS Systems Management Server や Proxy Server Commerce Server BizTalk Server Application Center Outlook CDO などの講習会も 担当 998 年度には Microsoft CTEC 現 CPLS トレーナー ゕワード Trainer of the Year を受賞 4