スクリプト タスクの実行
スクリプト タスクの実行
スクリプト タスクを利用すると、Visual C# 2010 や Visual Basic 2010 で記述したスクリプトを実行できるようになります。ここでは、Visual Basic 2010 を利用して簡単なスクリプトを記述し、実行できることを確認してみましょう。
- まずは、［スタート］メニューの［すべてのプログラム］から、［Microsoft SQL Server 2012］の［SQL Server Data Tools］をクリックして、SQL Server Data Tools を起動します。
- SQL Server Data Tools（Visual Studio 2010）が起動したら、［スタート ページ］の［新しいプロジェクト］をクリックして、新しいプロジェクトを作成します。
- ［新しいプロジェクト］ダイアログが表示されたら、次のように［インストールされたテンプレート］で［ビジネス インテリジェンス］の［Integration Services］をクリックして、「Integration Services プロジェクト」を選択します。
［名前］へ任意のプロジェクト名（画面は SSISoyo1）、［場所］へ任意の保存場所（画面は C:\）を入力して、［OK］ボタンをクリックします。これにより、Integration Services プロジェクトが作成されます。 - 次に、SSIS ツールボックスの［共通］カテゴリから［スクリプト タスク］を SSIS デザイナー上へドラッグ＆ドロップして配置します。
- 続いて、配置した［スクリプト タスク］をダブル クリックして、［スクリプト タスク エディター］ダイアログを表示します。
- このダイアログでは、次のように［ScriptLanguage］で「Microsoft Visual Basic 2010」を選択して、［スクリプトの編集］ボタンをクリックします。
これで、Visual Basic 2010 でスクリプトを記述できるようになります。 - 数秒後、次のように「スクリプト エディター」が起動されるので、「public Sub Main( )」内へ、以下のコードを記述します（コードが開かれていない場合は、プロジェクト エクスプローラーで［ScriptMain.vb］ファイルをダブル クリックして開きます）。
このコードにより、「Hello」という文字をメッセージ ボックスで表示できるようになります。
コードを記述後、ツールバーの［保存］ボタンをクリックしてスクリプトを保存し、スクリプト エディターを閉じます（［×］ボタンで終了します）。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に､スクリプトを実行するために、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。
配置されている［スクリプト タスク］が実行中に変わって、メッセージ ボックスに「Hello」と表示されることを確認できます。
［OK］ボタンをクリックすると、［スクリプト タスク］に緑のチェックマークが付いて、スクリプトの実行が成功したことを確認できます。 - 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
システム変数の利用
次に、スクリプト内で Integration Services のシステム変数を利用してみましょう。
- 配置した［スクリプト タスク］をダブル クリックします。
［スクリプト タスク エディター］ダイアログが表示されたら、［ReadOnlyVariables］の［...］ボタンをクリックします。 - これにより、次のように［変数の選択］ダイアログが表示されて、「System::」で始まるシステム変数が一覧されます。
今回は、この中から「System::UserName」をチェックして、［OK］ボタンをクリックします。UserName システム変数は､パッケージを実行しているユーザーの名前を取得することができます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［スクリプトの編集］ボタンをクリックします。
- ［スクリプト エディター］が開いたら、「public Sub Main( )」内のコードを、次のように変更します。
スクリプト タスク内では、「Dts.Variables("変数名")」と指定することで、変数を利用することができます。変数名の「System::UserName」は、大文字と小文字を区別するので、注意してください。このコードにより、メッセージ ボックスでユーザー名を表示できるようになります。
入力後、保存してスクリプト エディターを閉じます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に､スクリプトを実行するために、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。(とあるが、実行前に保存するという昔は当たり前だった習慣を復活させたほうがよさそう。ここで異常終了してコーディングが消えてしまった。）
- 配置されている［スクリプト タスク］が実行中に変わって、実行しているユーザー名（画面は MOON\Adminitrator）がメッセージ ボックスで表示されることを確認できます。
- 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
このように、スクリプト タスクを利用すると、変数を扱うことができます。スクリプト タスクでは、ユーザー定義の変数を扱うこともできますが、これについては Step2.3 で説明します。
スクリプト タスクからファイルへの書き込み
スクリプト タスクからファイルへの書き込み
次に、ファイルへの書き込みを行うスクリプトを作成してみましょう。
- まずは、配置した［スクリプト タスク］をダブル クリックして、［スクリプト タスク エディター］ダイアログを表示し、［スクリプトの編集］ボタンをクリックします。
- ［スクリプト エディター］が表示されたら、「public Sub Main( )」内のコードを、次のように変更します。
Dim sw As New System.IO.StreamWriter("C:\test.txt", False)
sw.Write("XXXXX")
sw.Close()このコードにより、「C:\text.txt」というファイルを作成して、「XXXXX」という文字を書き込むことができます。
入力後、保存してスクリプト エディターを閉じます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に、スクリプトを実行するために、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。
配置されている［スクリプト タスク］に緑のチェックマークが付いて、スクリプトの実行が成功したことを確認できます。 - 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
- 次に、Windows エクスプローラーを起動して、C:\text.txt ファイルをダブル クリックして開きます。
- 「XXXXX」という文字が書き込まれていることを確認できます。
このように、スクリプト タスクでは、Visual Basic 2010 または Visual C# 2010 を利用して、任意の .NET コードを記述することができます。
ユーザー定義の変数の利用
ユーザー定義の変数の利用
次に、ユーザー定義の変数を利用してみましょう。
- まずは、変数を定義します。次のように、［SSIS］メニューから［変数］をクリックして、［変数］ウィンドウを表示します。
- 続いて、ツールバーの［変数の追加］ボタンをクリックします。
- これにより、変数が追加できるようになるので、今回は、次のように［名前］を「var1」、［データ型］を「String」（文字列）へ指定した変数を追加します。
- 続いて、制御フローに配置している［スクリプト タスク］をダブル クリックします。
［スクリプト タスク エディター］ダイアログが表示されたら、［ReadWriteVariables］の［...］ボタンをクリックします（スクリプト内で変数へ値を代入するには、ReadOnly ではなく、ReadWrite～ へ変数を指定する必要があります）。 - これにより、［変数の選択］ダイアログで、変数の一覧が表示されるので、「User::var1」をチェックして、［OK］ボタンをクリックします（ユーザー定義の変数には、「User::」が付きます）。
- ［スクリプト タスク エディター］ダイアログへ戻ったら、［スクリプトの編集］ボタンをクリックします。
- ［スクリプト エディター］が表示されたら、「public Sub Main( )」内のコードを、次のように変更します。変数名「User::var1」は、大文字と小文字を区別するので、注意してください。
Dts.Variables("User::var1").Value = System.DateTime.Now.ToString()
このコードにより、スクリプト実行時の時刻（Now プロパティ）を変数「var1」へ格納できるようになります。
入力後、保存してスクリプト エディターを閉じます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に、SSIS ツールボックスを開いて、［スクリプト タスク］をもうひとつ、SSIS デザイナー上へドラッグ＆ドロップして配置します。
最初の［スクリプト タスク］の緑色の矢印を、新たに追加した［スクリプト タスク1］まで、ドラッグ＆ドロップして伸ばします。 - 追加した［スクリプト タスク1］をダブル クリックして、［スクリプト タスク エディター］ダイアログを表示します。
- ［スクリプト タスク エディター］ダイアログが表示されたら、［ScritLanguage］で「Microsoft Visual Basic 2010」を選択します。
- 次に、［ReadOnlyVariables］の［...］ボタンをクリックします。
- ［変数の選択］ダイアログが表示されたら、変数の一覧から、「User::var1」をチェックして、［OK］ボタンをクリックします。
- ［スクリプト タスク エディター］ダイアログへ戻ったら、［スクリプトの編集］ボタンをクリックします。
- ［スクリプト エディター］では、「public Sub Main( )」内のコードを、次のように変更します（変数名の大文字と小文字に注意してください）。
MessageBox.Show(Dts.Variables("User::var1").Value.ToString())
このコードにより、変数「var1」の値をメッセージ ボックスで表示できるようになります。
入力後、保存してスクリプト エディターを閉じます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。
１つ目の［スクリプト タスク］が緑色、2つ目の［スクリプト タスク1］が実行中に変わって、スクリプト タスクを実行した時の日時がメッセージ ボックスで表示されることを確認できます。
［OK］ボタンをクリックすると、［スクリプト タスク1］に緑のチェックマークが付いて、スクリプトの実行が成功したことを確認できます。 - 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
SQL 実行タスクの結果を変数へ格納
SQL 実行タスクの結果を変数へ格納
次に、「SQL 実行タスク」を利用して SQL を実行し、その結果を変数へ格納したり、その変数をデータ フロー タスクのパラメーターへ受け渡してデータ転送を実行してみましょう。
- まずは、SSIS ツールボックスの［お気に入り］カテゴリから［SQL 実行タスク］を SSIS デザイナー上へドラッグ＆ドロップして配置します。
- 次に、［SSIS］メニューから［変数］をクリックします。
- ［変数］ウィンドウが表示されたら、前のステップで作成した変数「var1」を選択して、［変数の削除］ボタンをクリックします。
- 続いて、［変数の追加］ボタンをクリックし、新しく変数を追加します。
［名前］には「v1」と入力して、［データ型］は既定値の「Int32」を選択します。Int32 は、SQL Server での int 型に相当するデータ型です。 - 次に、もう一度［変数の追加］ボタンをクリックして、もうひとつ変数を追加します。
［名前］は「v2」とし、［データ型］は既定値の「Int32」を選択します。 - 次に、SSIS デザイナーへ配置した［SQL 実行タスク］をダブル クリックします。
- ［SQL 実行タスク エディター］ダイアログが表示されたら、［Connection］で「新しい接続」をクリックして、［OLE DB 接続マネージャーの構成］ダイアログを表示し、［新規作成］ボタンをクリックします。
- ［接続マネージャー］ダイアログが表示されたら、［サーバー名］へ SQL Server の名前（画面は moon）を入力し、［データベース名の選択または入力］で「sampleDB」データベースを選択して、［OK］ボタンをクリックします。
［OLE DB 接続マネージャーの構成］ダイアログへ戻ったら、［OK］ボタンをクリックします。 - 次に、［SQLStatement］の［...］ボタンをクリックして、［SQL クエリの入力］ダイアログを表示し、次のように SQL を入力します。この SQL は、sampleDB データベース内にある「社員」テーブルから、「給与」の最大値と最小値を取得するものです。
SELECT MAX(給与) AS maxsal, MIN(給与) AS minsal FROM 社員 - 続いて、［ResultSet］で、クエリ結果の形式を指定します。このクエリの結果は、1行のみを返すので、「単一行」を選択します。
- 次に、［結果セット］ページをクリックして開きます。［追加］ボタンをクリックして、［結果名］へ給与の最大値の列名である「maxsal」を入力し、［変数名］で「User::v1」が選択されていることを確認します。
これにより、SELECT ステートメントで取得した maxsal（MAX(給与)）の値を変数 v1 へ格納できるようになります。 - 続いて、もう一度［追加］ボタンをクリックして、［結果名］へ給与の最小値の列名である「minsal」を入力し、［変数名］で「User::v2」を選択して、［OK］ボタンをクリックします。
- 次に、1つ目の［スクリプト タスク］を右クリックして、［無効化］をクリックし、［スクリプト タスク］を無効化しておきます。
- 続いて、［SQL 実行タスク］の緑色の矢印を［スクリプト タスク1］までドラッグ＆ドロップして伸ばします。
- 次に、［スクリプト タスク1］をダブルクリックします。
- ［スクリプト タスク エディター］ダイアログが表示されたら、［ReadOnlyVariables］で［...］ボタンをクリックして、［変数の選択］ダイアログを表示し、追加した「User::v1」と「User::v2」の変数をチェックして、［OK］ボタンをクリックします。
- ［スクリプト タスク エディター］ダイアログへ戻ったら、［スクリプトの編集］ボタンをクリックします。
- ［スクリプト エディター］では、「public Sub Main( )」内のコードを、次のように変更します（変数名の、大文字と小文字に注意してください）。
MessageBox.Show(Dts.Variables("User::v1").Value.ToString() _
& " : " & Dts.Variables("User::v2").Value.ToString())
このコードにより、変数「v1」と「v2」の値をメッセージ ボックスで表示できるようになります。入力後、保存してスクリプト エディターを閉じます。 - ［スクリプト タスク エディター］ダイアログへ戻ったら、［OK］ボタンをクリックして閉じます。
- 次に、スクリプト タスクを実行するために、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。
次のように、［SQL 実行タスク］に緑のチェックマーク、［スクリプト タスク1］が実行中へ変わって、社員テーブルの給与の最大値「700000」と最小値「300000」がメッセージ ボックスで表示されることを確認できます。
［OK］ボタンをクリックすると、［スクリプト タスク1］に緑のチェックマークが付いて、スクリプトの実行が成功したことを確認できます。
このように、SQL 実行タスクで実行した結果は、変数へ格納することができ、それはスクリプト タスクなどパッケージ内で再利用していくことができます。 - 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
SQL ステートメントのパラメーター化と変数の引き渡し
SQL ステートメントのパラメーター化と変数の引き渡し
次に、SQL 実行タスクの結果を格納した変数を、データ フロー タスク内の SQL ステートメントの「パラメーター」へ引き渡して、実行できるようにしてみましょう。
- まずは、［スクリプト タスク1］を右クリックして、［無効化］をクリックし、［スクリプト タスク1］を無効にしておきます。
- 次に、SSIS ツールボックスを開いて、［お気に入り］カテゴリから［データ フロー タスク］をドラッグ＆ドロップして SSIS デザイナー上へ配置します。
- 次に、［SQL 実行タスク］の緑色の矢印を、［データ フロー タスク］まで、ドラッグ＆ドロップして伸ばします。
- 続いて、［データ フロー タスク］をダブル クリックして、［データ フロー］タブを表示します。
- ［データ フロー］タブが表示されたら、SSIS ツールボックスの［その他の変換元］カテゴリから［OLE DB ソース］を SSIS デザイナー上へドラッグ＆ドロップして配置します。
配置した［OLE DB ソース］をダブル クリックして、［OLE DB ソース エディター］を開き、［OLE DB 接続マネージャー］で「サーバー名.sampleDB」が選択されていることを確認します。
［データ アクセス モード］では、「SQL コマンド」を選択します。これにより、［SQL コマンド テキスト］が表示され、SQL が入力できるようになるので、次のように SQL を記述します。
SELECT * FROM 社員 WHERE 給与 = ?
「給与 = ?」と記述することで、パラメーターが 1 つ（パラメーター0）自動作成されます。
SQL を記述後、［パラメーター］ボタンをクリックします。 - ［クエリ パラメーターの設定］ダイアログが表示されたら、自動作成されたパラメーター「パラメーター0」の［変数］で「User::v2」変数を選択して割り当てます。
これで、「給与 = ?」の ? へ代入する値を変数 v2（最小給与が格納されている変数）にすることができます。
設定後、［OK］ボタンをクリックして、ダイアログを閉じます。［OLE DB ソース エディター］へ戻ったら、［OK］ボタンをクリックして閉じます。 - 次に、SSIS ツール ボックスの［その他の変換先］カテゴリから［SQL Server 変換先］を SSIS デザイナー上へドラッグ＆ドロップして配置します。
続いて、［OLE DB ソース］の青色の矢印を［SQL Server 変換先］まで、ドラッグ＆ドロップして伸ばします。 - 次に、［SQL Server 変換先］をダブル クリックして、［SQL 変換先エディター］を開き、［接続マネージャー］で「サーバー名.samplDB」が選択されていることを確認します。
［テーブルまたはビューを使用］で［新規作成］ボタンをクリックします。 - これにより、［テーブルの作成］ダイアログが表示されるので、テーブル名を「最小給与社員」へ変更して、［OK］ボタンをクリックします。
- ［SQL 変換先エディター］へ戻ったら、［マッピング］ページをクリックして開き、［OK］ボタンをクリックします。
- 次に、［制御フロー］タブをクリックして開いてから、ツールバーの［デバッグ開始］ボタンをクリックして、パッケージを実行します。
［SQL 実行タスク］と［データ フロー タスク］に緑のチェックマークが付いて、パッケージの実行が成功したことを確認できます。 - 確認後、ツールバーの［デバッグの停止］ボタンをクリックして、デバッグを終了します。
- 転送されたデータを確認するには、Management Studio から、［最小給与社員］テーブルを右クリックして、［上位 1000 行の選択］をクリックし、データを確認します。
給与が最小の社員が格納されていることを確認できます。
このように、データ フロー タスク内の SQL ステートメントは、パラメーター化することができ、また、それに対して変数を代入して実行することができます。
0 件のコメント:
コメントを投稿