2013年3月12日火曜日

◆SSIS デザイナーの基本操作

作成するパッケージの概要
データ変換の概要
この STEPでは、SSIS デザイナーを利用して、次のようなデータ変換を行うパッケージ(Excel 2010 ファイルから SQL Server へのデータ転送時に変換処理を追加したパッケージ)を作成します。
image
作成するパッケージは、次のような構成になります。
image
データベースの作成
転送先となるデータベースの作成「ssisdb2」
この STEP では、SSIS デザイナーを使用して、Excel データを SQL Server 内へ取り込んでいくので、まずは、取り込み先(転送先)となるデータベース「ssisdb2」を作成します。
次のようにManagement Studio で[データベース]フォルダーを右クリックして、[新しいデータベース]をクリックし、「ssisdb2」という名前のデータベースを作成します。
image
SSIS デザイナーの起動
SSIS デザイナーの起動
  1. SSIS デザイナーを起動するには、[スタート]メニューの[すべてのプログラムから、[Microsoft SQL Server 2012]の[SQL Server Data Tools]をクリック
    image
  2. SQL Server Data Tools(Visual Studio 2010)が起動したら、[スタート ページ]の[新しいプロジェクト]をクリックして、新しいプロジェクトを作成します。
    image
  3. [新しいプロジェクト]ダイアログでは、[インストールされたテンプレート]で「ビジネス インテリジェンス」の[Integration Services]を選択して、「Integration Services プロジェクト」を選択します。
    image
    [名前]へ任意のプロジェクト名(画面は Integration Services プロジェクト2)、[場所]へ任意の保存場所(画面は C:\)へ設定して、[OK]ボタンをクリックします。これにより、Integration Services プロジェクトが作成されて、次のように画面が表示されます。
    image
データ フロー タスクによるデータの転送
データ フロー タスクによるデータの転送
まずは、データ変換を伴わない、単純なデータ転送を行うパッケージを作成してみましょう。データ転送は、[データ フロー タスク]を利用して行うことができます
  1. データ フロー タスクを追加するには、次のように SSIS ツールボックスから[お気に入り]カテゴリの[データ フロー タスク]を選択し、SSIS デザイナー上へドラッグ&ドロップします。
    image
  2. 次に、データ転送の詳細(転送元や転送先など)を設定するために、次のように[データ フロー タスク]をダブル クリックします。
    image
    これにより、次のように[データ フロー]タブが表示されて、SSIS ツールボックスがデータ転送に関するコンポーネントの一覧へ変更されます。
    image
  3. 転送元(ソース)データとして Excel ファイルを指定するには、次のように SSIS ツールボックスの[その他の変換元]カテゴリの中から、[OLE DB ソース]を SSIS デザイナー上へドラッグ&ドロップします。
    image
    続いて、配置した[OLE DB ソース]をダブル クリックします。
  4. これにより、[OLE DB ソース エディター]ダイアログが表示されるので、転送元の Excel ファイルを指定するために[新規作成]ボタンをクリックします。
    image
  5. [OLE DB 接続マネージャーの構成]ダイアログが表示されたら、[新規作成]ボタンをクリックします。
    image
    [接続マネージャー]ダイアログが表示されるので、[プロバイダー]で「Microsoft Office 12.0 Access Database Engine OLE DB Provider」を選択します。
    image
  6. 続いて、[サーバー名またはファイル名]へサンプル スクリプト内の「Employee.xlsx」ファイルへのパスを入力します。
    image
    信じがたいことにファイル選択ダイアログがついていないので直接パスを直打ちするしかない。(MSのサーバー製品はこのような信じがたいことがよくある。SQLServerでさえも・・・)
  7. 次に、[すべて]ページをクリックして開き、[Extended Properties]へ「Excel 12.0」と入力し、[OK]ボタンをクリックします(Excel 12.0 は Excel 2010 の内部バージョン番号です)。
    image
  8. [OLE DB ソース エディター]ダイアログへ戻ったら、次のように[テーブル名またはビュー]で、転送元となるデータが格納されている Excel のワーク シートを選択します(ここで表示されるシート名は、実際のワーク シート名に「$」マークをつけたものになります)
    image
    今回のデータは、Sheet1 へ入れているので、「Sheet1$」を選択して、[プレビュー]ボタンをクリックします。これにより、[クエリ結果のプレビュー]ダイアログが表示されて、転送元のデータを確認することができます。確認後、[閉じる]ボタンをクリックして、ダイアログを閉じます。
    (自分の環境では以下のように読み込みがうまくいかなかった。きっとエラーになっているのだろうがエラーハンドリングが何もできていなのは残念な作りだ)
    image

    image
  9. 次に、転送先となる SQL Server 上のデータベースを指定するために、次のように SSIS ツールボックスの[その他の変換先]カテゴリから、[SQL Server 変換先]を SSIS デザイナー上へドラッグ&ドロップします。
    image
  10. 次に、転送元と転送先を関連付けるために、SSIS デザイナー上にある[OLE DB ソース]をクリックして「青」と「赤」の矢印を表示し、「青の矢印」を[SQL Server 変換先]まで、ドラッグ&ドロップして伸ばします。
    image
    image
  11. 次に、転送先の詳細設定を行うために、[SQL Server 変換先]をダブルクリックします。
    これにより、[SQL 変換先エディター]ダイアログが表示されるので、次のように[接続マネージャー]ページで[新規作成]ボタンをクリックします。
    image
  12. [OLE DB 接続マネージャーの構成]ダイアログが表示されたら、さらに[新規作成]ボタンをクリックします。
    image
  13. [接続マネージャー]ダイアログでは、次のように[サーバー名]へ転送先となる SQL Server の名前を入力して、[データベース名の選択または入力]で「ssisdb2」を選択し、[OK]ボタンをクリックします。
    image
  14. [OLE DB 接続マネージャーの構成]ダイアログへ戻ったら、[データ接続]で、「サーバー名.ssisdb2」が選択されていることを確認して、[OK]ボタンをクリックします。
    image
  15. [SQL 変換先エディター]ダイアログへ戻ったら、[テーブルまたはビューを使用]で[新規作成]ボタンをクリックします。
    image
    これにより、[テーブルの作成]ダイアログが表示されて、転送元のデータをもとに、SQL Server 上へテーブルを作成できるようになります。ここで表示される CREATE TABLE ステートメントのテーブル名を「社員」へ変更して、[OK]ボタンをクリックします。これにより、「社員」テーブルを ssisdb2 データベース内へ作成することができます([OK]ボタンをクリックしたときに実際にテーブルが作成されます)。
  16. 続いて、次のように[マッピング]ページをクリックすると、転送元のデータの各列と、転送先のデータの各列とのマッピング(対応)を設定することができます。
    image
    [使用できる入力列]と[使用できる変換先列]で、同じ名前の列が割り当てられていることを確認して、[OK]ボタンをクリックします。
  17. ここまでの設定を確認するために、ツールバーの[デバッグ開始]ボタンをクリックして、データの転送を実行します。
    image
    配置されているオブジェクトすべてに緑のチェックマークが付くと、データの転送が成功です。
  18. 転送が成功したら、ツールバーの[デバッグの停止]ボタンをクリックして、デバッグを終了します。
    image
  19. ここまでの手順を保存するために、[ファイル]メニューの[すべてを保存]をクリックして、プロジェクトを保存します。
    image
派生列コンポーネントによるデータ変換
次に、単純なデータ転送(コピー)だけでなく、データ変換を追加していきましょう。ここでは、「姓」と「名」列から「氏名」列へ変換し、「性別コード」列からは、「1」を「男性」へ、「2」を「女性」へと変換するような「性別」列を作成します。このようなデータ変換は、派生列コンポーネントを利用して簡単に行うことができます。
  1. まずは、前の手順で設定した[OLE DB ソース]から、[SQL Server 変換先]へ向かって出ている "青色の矢印" を右クリックして、[削除]をクリックします。これにより、矢印が削除されます。
    image
  2. 次に、データ変換を行うためのコンポーネントとなる[派生列]を[OLE DB ソース]と[SQL Server 変換先]の間へ配置します。このコンポーネントは、SSIS ツールボックスの[共通]カテゴリにあります。
    image
  3. 次に、転送元と派生列を関連付けるために、SSIS デザイナー上にある[OLE DB ソース]をクリックして "青色の矢印" を表示し、この矢印を[派生列]まで、ドラッグ&ドロップして伸ばします。
    image
  4. 次に、データ変換を定義するために、次のように[派生列]コンポーネントをダブル クリックします。
    image
    これにより、[派生列変換エディター]ダイアログが表示されるので、[列]フォルダーを展開します。これで、転送元の列(社員番号や姓、名など)が一覧されるようになります。
  5. 今回は、「姓」列と「名」列をもとに「氏名」列を作成するので、次のように[派生列名]に「氏名」と入力して、[派生列]が「新しい列として追加」になっていることを確認します。
    image
    次に、「姓」と「名」列を文字列連結するために、[式]へ、「姓」と「名」列をドラッグ&ドロップして配置し、次のように式を入力します。
    このように、派生列コンポーネントを利用すると、既存の列データを元に、新しい列(元データから派生した列)を作成できるようになります。
  6. 続いて、「性別コード」列をもとに「性別」列を作成するために、次のように[派生列名]に「性別」と入力して、[派生列]が「新しい列として追加」になっていることを確認します。
    image
    次に「性別コード」が「1」の場合は「男性」、「2」の場合は「女性」になるように「性別」列を作成するために、「性別コード」列を[式]へドラッグ&ドロップして配置し、次のように式を入力します。
    [性別コード] == 1 ? "男性" : "女性"
  7. 次に、派生列として作成した「氏名」と「性別」列が SQL Server へ転送されるようにします。次のように[派生列]をクリックして "青色の矢印" を表示し、この矢印を[SQL Server 変換先]までドラッグ&ドロップして伸ばします。
    image
  8. 次に、[SQL Server 変換先]をダブル クリックします。
    image
  9. [SQL 変換先エディター]ダイアログが表示されたら、[接続マネージャー]で「サーバー名.ssisdb2」が選択されていることを確認します。続いて、[テーブルまたはビューを使用]で[新規作成]ボタンをクリックします。
    image
    これにより、[テーブルの作成]ダイアログが表示されて、転送元のデータと派生列コンポーネントをもとに、CREATE TABLE ステートメントが自動生成されます。今回は、この SQL ステートメントを次のように変更します。
    image
    image
    これにより、「社員2」テーブルを ssisdb2 データベース内へ作成することができます([OK]ボタンをクリックしたときに実際にテーブルが作成されます)。
  10. 続いて、次のように[マッピング]ページをクリックすると、転送元のデータの各列と転送先のデータの各列とのマッピング(対応)を設定することができます。
    image前の手順で作成した「氏名」列がテーブルへ転送されるように、[使用できる入力列]の「氏名」列から[使用できる変換先列]の「氏名」列へドラッグ&ドロップします。同様に、「性別」列に対しても同じ操作を行います。
    これにより、[使用できる入力列]から[使用できる変換先列]へ 2 本の線(マッピング)が追加され、作成した「氏名」列と「性別」列を、「社員2」テーブルの「氏名」列と「性別」列へそれぞれ割り当てることができます。
  11. ここまでの設定を確認するために、ツールバーの[デバッグ開始]ボタンをクリックして、データ転送を実行します。
    image
    配置されているオブジェクトすべてに緑のチェックマークが付くと、データの転送が成功です。
    転送が成功したら、ツールバーの[デバッグの停止]ボタンをクリックして、デバッグを終了します。
  12. 次に、ここまでの手順を保存するために、[ファイル]メニューの[すべてを保存]をクリックして、プロジェクトを保存します。
    image

参照コンポーネントによる別テーブルのデータ取得
参照コンポーネントによる別テーブルのデータ取得
次に、社員データの「部門番号」から、「部門」テーブルにある「部門名」を取得(参照)してデータを転送するようにパッケージを変更します。他のテーブルのデータを参照するには、「参照」(Lookup)コンポーネントを利用します。
image
「部門」テーブルの準備
この Step を実施するには、サンプル スクリプト内にある「bumon.txt」ファイルを実行して、「ssisdb2」データベース内に「部門」テーブルを作成しておく必要があります。
image
参照コンポーネントの追加
  1. まずは、前の手順で設定した[派生列]から[SQL Server変換先]へ向かって出ている "青色の矢印" を右クリックして、[削除]をクリックします。これにより矢印が削除されます。
    image
  2. 次に、SSIS ツールボックスの[共通]カテゴリの中から、[参照]コンポーネントを SSIS デザイナー上へドラッグ&ドロップします。
    image
  3. 次に、SSIS デザイナー上にある[派生列]コンポーネントをクリックして "青色の矢印" を表示し、この矢印を[参照]コンポーネントまで、ドラッグ&ドロップして伸ばします。
    image
  4. 次に、取得するデータを指定するために、[参照]コンポーネントをダブル クリックします。
    image
  5. [参照変換エディター]ダイアログが表示されたら、[接続]ページをクリックして開きます。
    image
    [接続]ページが表示されたら、[OLE DB 接続マネージャー]で「サーバー名.ssisdb2」を選択して、[OK]ボタンをクリックします。
  6. 続いて、[テーブルまたはビューを使用する]で参照先のテーブルとなる「部門」テーブルを選択します。
    image
    部門テーブルのデータを確認するために[プレビュー]ボタンをクリックします。確認後、[閉じる]ボタンをクリックします。
  7. 続いて、[列]ページをクリックして、参照する列を設定します。今回は、[使用できる入力列]の「部門番号」列の参照先が、[使用できる参照列]の「部門番号」列となるように、ドラッグ&ドロップします。
    image
    しかし、「データ型が浮動小数点(float)型のため、参照できない」という主旨のエラーメッセージが表示され、正しく設定することができません。参照コンポーネントは、データ型が一致しないと参照することができないためです(転送元の「部門番号」列は float 型で、参照先の「部門」テーブルの「部門番号」列は int 型で、データ型が一致していません)。そこで、ここでは、ひとまず[OK]ボタンをクリックして、エラー メッセージを閉じます。さらに、[接続]ページで[OK]ボタンをクリックして、[参照変換エディター]ダイアログを閉じます。
  8. 転送元の「部門番号」列のデータ型を float 型から int 型へ変更するには、次のように[OLE DB ソース]を右クリックして、[詳細エディターの表示]をクリックします。
    image
  9. これにより、[OLE DB ソースの詳細エディター]ダイアログが表示されるので、次のように[入力プロパティと出力プロパティ]タブをクリックします。
    image
    このダイアログでは、[OLE DB ソースの出力]の[出力列]を展開して、Excel ファイルから転送される列を一覧します。この一覧から「部門番号」列を選択して、表示されるプロパティの中から、[Data Type](データ型)を探します。この値は、「倍精度浮動小数点数」(SQL Server での float 型に相当するデータ型)へ設定されているので、「4バイト符号付き整数」(SQL Server での int 型に相当するデータ型)へ変更します。
    データ型の変更後、[OK]ボタンをクリックして、ダイアログを閉じます。
  10. 次に、先ほど失敗した参照列の設定を再度行うために、[参照]コンポーネントをダブル クリックします。
    image
  11. [参照変換エディター]ダイアログが表示されたら、[列]ページをクリックして開きます。
    image
    [使用できる入力列]の「部門番号」列を[使用できる参照列]の「部門番号」列へドラッグ&ドロップすると、今度はエラーメッセージが表示されずに線が追加されます。続いて、取得したい列を指定するために、[使用できる参照列]の「部門名」列のチェック ボックスをチェックし、[OK]ボタンをクリックします。 これで部門テーブルから部門名を参照(取得)できるようになります。
  12. 次に、取得した部門名が SQL Server へ転送されるように、[参照]コンポーネントの青色の矢印を[SQL Server 変換先]までドラッグ&ドロップして伸ばします。
    image
    これにより、[入出力の選択]ダイアログが表示されるので、[出力]で「参照の一致出力」を選択して、[OK]ボタンをクリックします。これで、[参照]コンポーネントで参照した値(参照元と参照先の部門番号が一致する部門名)を[SQL Server 変換先]へ渡せるようになります。
転送先のテーブルの変更
  1. 次に、転送先のテーブルを変更するために、[SQL Server 変換先]をダブル クリックして、[SQL 変換先エディター]ダイアログを開きます。
    image
  2. [接続マネージャー]で「サーバー名.ssisdb2」が選択されていることを確認し、[テーブルまたはビューを使用]で[新規作成]ボタンをクリックします。
    image
    これにより、[テーブルの作成]ダイアログが表示されて、転送元のデータと派生列、参照コンポーネントで参照した列をもとに、CREATE TABLE ステートメントが自動生成されます。今回は、このステートメントを次のように変更します。
    image
    ステートメント変更後、[OK]ボタンをクリックすると、「社員3」テーブルが作成されます。
  3. [SQL 変換先エディター]ダイアログに戻ったら、次のように[マッピング]ページをクリックします。
    image
    [参照]コンポーネントで参照した「部門名」列がテーブルへ転送されるように、[使用できる入力列]の「部門名」列から[使用できる変換先列]の「部門名」列へドラッグ&ドロップします。線が追加されたことを確認して、[OK]ボタンをクリックします。
  4. ここまでの設定を確認するために、ツールバーの[デバッグ開始]ボタンをクリックして、パッケージを実行します。
  5. 転送が成功したら、ツールバーの[デバッグの停止]ボタンをクリックして、デバッグを終了します。
  6. 最後に、ここまでの手順を保存するために、次のように[ファイル]メニューの[すべてを保存]をクリックして、プロジェクトを保存します。
データ ビューアーによる転送中のデータの表示
データ ビューアーによる転送中のデータの表示
データの転送は、ここまで作成してきたように、矢印の順にそれぞれのコンポーネントを通って行われます。SSIS デザイナーでは、「データ ビューアー」機能を利用すると、コンポーネント間を転送されるデータを確認できるようになります。
image
データ ビューアーは、派生列や参照コンポーネントによるデータ変換処理が正しく行われているかどうかの確認目的(デバッグ目的)として利用できるので、大変便利です。
データ ビューアーの有効化
それでは、これを試してみましょう。ここでは、これまで作成してきた「Integration Services プロジェクト2」プロジェクトのパッケージを引き続き利用します。
  1. 最初に、[OLE DB ソース]から[派生列]へ転送されるデータを確認するためのデータ ビューアーを設定してみましょう。次のように SSIS デザイナー上の[OLE DB ソース]と[派生列]の間の矢印を右クリックして、[データ ビューアーの有効化]をクリックします。
    image
    これにより、データ ビューアー アイコンが追加されることを確認できます。
  2. 同様にして、[派生列]と[参照]コンポーネントとの間にもデータ ビューアーを設定しましょう。次のように SSIS デザイナー上の[派生列]と[参照]の間の矢印を右クリックして[データ ビューアーの有効化]をクリックします。
    image
  3. 次に、ツールバーの[デバッグ開始]ボタンをクリックして、パッケージを実行します。
    image
  4. すると、[派生列]コンポーネントが実行中になってデータ転送がここで一時的に停止し、[データ ビューアー]ウィンドウが表示されます。
    image
    データ ビューアーでは、[OLE DB ソース]から[派生列]に転送されてきたデータを確認することができます。
    このデータは、元の Excel データと同じであることを確認し、「▶」ボタンをクリックして、転送を再開します。
  5. 今度は、[参照]コンポーネントが実行中になってデータ転送がここで再び一時的に停止し、次のように[データ ビューアー]ウィンドウが表示されます。
    データ ビューアーでは、[派生列]から[参照]コンポーネントへ転送されてきたデータを確認することができます。
    派生列(新しい列)として作成した「氏名」列と「性別」列が追加されていることを確認して、「▶」ボタンをクリックし、転送を再開します。
  6. すべてのコンポーネントに緑のチェックマークが付くと、データの転送が完了です。転送完了後、ツールバーの[デバッグの停止]ボタンをクリックして、デバッグを終了します。
    image
    このように、データ ビューアーを利用すると、コンポーネント間を転送されるデータを確認することができ、派生列や参照コンポーネントなどのデータ変換処理が正しく行われているかどうかを確認する目的として利用できるので、大変便利です。
データ ビューアーの無効化
  1. 設定したデータ ビューアーを無効化したい場合は、次のように青の矢印を右クリックして[データ ビューアーの無効化]をクリックします。
    image
  2. もう 1つのデータ ビューアーに対しても、同様の操作をして、無効化しておきます。

0 件のコメント:

コメントを投稿