スクリプトタスクを使用した Excel ファイルの操作

[アーティクル]
12/15/2008

新規 : 2006 年 4 月 14 日

SQL Server 2005 Integration Services (SSIS) は、Microsoft Excel ファイル形式のスプレッドシートに格納されたデータを操作するための Excel 接続マネージャ、Excel ソース、および Excel 変換先を提供します。このトピックで説明する方法では、スクリプトタスクを使用して、使用可能な Excel のデータベース (ワークブックファイル) およびテーブル (ワークシートおよび名前付き範囲) に関する情報を取得します。これらのサンプルに簡単な変更を加えて、Microsoft Jet OLE DB プロバイダによってサポートされる他のすべてのファイルベースデータソースを操作することができます。

サンプルをテストするためのパッケージの構成

例 1 : Excel ファイルが存在するかどうかを確認する

例 2 : Excel テーブルが存在するかどうかを確認する

例 3 : フォルダ内の Excel ファイルの一覧を取得する

例 4 : Excel ファイル内のテーブルの一覧を取得する

サンプルの結果の表示

メモ :
複数のパッケージでより簡単に再利用できるタスクを作成する場合は、このスクリプトタスクサンプルのコードを基にした、カスタムタスクの作成を検討してください。詳細については、「カスタムタスクの開発」を参照してください。

サンプルをテストするためのパッケージの構成

このトピックのすべてのサンプルをテストする単一のパッケージを構成することができます。これらのサンプルでは、同じパッケージ変数と同じ .NET Framework クラスを数多く使用します。

このトピックの例で使用するパッケージを構成するには

Business Intelligence Development Studio で新しい Integration Services プロジェクトを作成し、編集のために既定のパッケージを開きます。
変数。[変数] ウィンドウを開き、次の変数を定義します。
- String 型の ExcelFile。既存の Excel ワークブックの完全なパスとファイル名を入力します。
- String 型の ExcelTable。ExcelFile 変数の値で指定されたワークブック内の既存のワークシートまたは名前付き範囲の名前を入力します。この値では大文字と小文字が区別されます。
- Boolean 型の ExcelFileExists。
- Boolean 型の ExcelTableExists。
- String 型の ExcelFolder。少なくとも 1 つの Excel ワークブックを含むフォルダの完全なパスを入力します。
- Object 型の ExcelFiles。
- Object 型の ExcelTables。
Imports ステートメント。ほとんどのコードサンプルでは、スクリプトファイルの先頭で次の .NET Framework 名前空間のいずれかまたは両方をインポートする必要があります。
- ファイルシステム操作用の System.IO。
- Excel ファイルをデータソースとして開くための System.Data.OleDb。
参照。Excel ファイルからスキーマ情報を読み取るコードサンプルでは、スクリプトプロジェクトで System.Xml 名前空間への追加の参照が必要です。

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

この例では、ExcelFile 変数で指定された Excel ワークブックファイルが存在するかどうかを判断し、その結果を ExcelFileExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を ExcelFileExists に変更します。
[スクリプトタスクエディタ] を開きます。
[スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFile を追加し、ReadWriteVariables の一覧に ExcelFileExists を追加します。
[スクリプトのデザイン] をクリックして、スクリプトエディタを開きます。
スクリプトファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。
Sub Main 内のすべてのコードを、次のコードに置き換えます。

例 1 のコード

    Dim fileToTest As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    If File.Exists(fileToTest) Then
      Dts.Variables("ExcelFileExists").Value = True
    Else
      Dts.Variables("ExcelFileExists").Value = False
    End If

    Dts.TaskResult = Dts.Results.Success

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

この例では、ExcelTable 変数で指定された Excel ワークシートまたは名前付き範囲が ExcelFile 変数で指定された Excel ワークブックファイル内に存在するかどうかを判断し、その結果を ExcelTableExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を ExcelTableExists に変更します。
[スクリプトタスクエディタ] を開きます。
[スクリプト] タブで、ReadOnlyVariables の一覧に ExcelTable と ExcelFile を追加し、ReadWriteVariables の一覧に ExcelTableExists を追加します。
[スクリプトのデザイン] をクリックして、スクリプトエディタを開きます。
スクリプトプロジェクトに System.Xml アセンブリへの参照を追加します。
スクリプトファイルの先頭に、System.IO 名前空間と System.Data.OleDb 名前空間の Imports ステートメントを追加します。
Sub Main 内のすべてのコードを、次のコードに置き換えます。

例 2 のコード

    Dim fileToTest As String
    Dim tableToTest As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim excelTables As DataTable
    Dim excelTable As DataRow
    Dim currentTable As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    tableToTest = Dts.Variables("ExcelTable").Value.ToString

    Dts.Variables("ExcelTableExists").Value = False
    If File.Exists(fileToTest) Then
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & fileToTest & _
        ";Extended Properties=Excel 8.0"
      excelConnection = New OleDbConnection(connectionString)
      excelConnection.Open()
      excelTables = excelConnection.GetSchema("Tables")
      For Each excelTable In excelTables.Rows
        currentTable = excelTable.Item("TABLE_NAME").ToString
        If currentTable = tableToTest Then
          Dts.Variables("ExcelTableExists").Value = True
        End If
      Next
    End If

    Dts.TaskResult = Dts.Results.Success

例 3 の説明 : フォルダ内の Excel ファイルの一覧を取得する

この例では、ExcelFolder 変数の値で指定されたフォルダ内で検索された Excel ファイルの一覧を配列に代入し、その配列を ExcelFiles 変数にコピーします。Foreach from Variable 列挙子を使用して、配列内のファイルを繰り返し処理することができます。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を GetExcelFiles に変更します。
[スクリプトタスクエディタ] を開きます。
[スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFolder を追加し、ReadWriteVariables の一覧に ExcelFiles を追加します。
[スクリプトのデザイン] をクリックして、スクリプトエディタを開きます。
スクリプトファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。
Sub Main 内のすべてのコードを、次のコードに置き換えます。

例 3 のコード

    Const FILE_PATTERN As String = "*.xls"

    Dim excelFolder As String
    Dim excelFiles As String()

    excelFolder = Dts.Variables("ExcelFolder").Value.ToString
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)

    Dts.Variables("ExcelFiles").Value = excelFiles

    Dts.TaskResult = Dts.Results.Success

代替ソリューション

スクリプトタスクを使用して Excel ファイルの一覧を配列に集める代わりに、ForEach File 列挙子を使用してフォルダ内のすべての Excel ファイルを繰り返し処理することもできます。詳細については、「Excel のファイルおよびテーブルをループ処理する方法」を参照してください。

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

この例では、ExcelFile 変数の値で指定された Excel ワークブックファイル内で検索されたワークシートまたは名前付き範囲の一覧を配列に代入し、その配列を ExcelTables 変数にコピーします。Foreach from Variable 列挙子を使用して、配列内のテーブルを繰り返し処理することができます。

メモ :
Excel ワークブック内のテーブルの一覧には、ワークシート ($ というサフィックスが付きます) と名前付き範囲の両方が含まれます。ワークシートまたは名前付き範囲のみを一覧からフィルタ選択する必要がある場合は、そのためのコードを追加する必要があります。

このスクリプトタスクの例を構成するには

パッケージに新しいスクリプトタスクを追加し、名前を GetExcelTables に変更します。
[スクリプトタスクエディタ] を開きます。
[スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFile を追加し、ReadWriteVariables の一覧に ExcelTables を追加します。
[スクリプトのデザイン] をクリックして、スクリプトエディタを開きます。
スクリプトプロジェクトに System.Xml 名前空間への参照を追加します。
スクリプトファイルの先頭に、System.Data.OleDb 名前空間の Imports ステートメントを追加します。
Sub Main 内のすべてのコードを、次のコードに置き換えます。

例 4 のコード

    Dim excelFile As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim tablesInFile As DataTable
    Dim tableCount As Integer = 0
    Dim tableInFile As DataRow
    Dim currentTable As String
    Dim tableIndex As Integer = 0

    Dim excelTables As String()

    excelFile = Dts.Variables("ExcelFile").Value.ToString
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & excelFile & _
        ";Extended Properties=Excel 8.0"
    excelConnection = New OleDbConnection(connectionString)
    excelConnection.Open()
    tablesInFile = excelConnection.GetSchema("Tables")
    tableCount = tablesInFile.Rows.Count
    ReDim excelTables(tableCount - 1)
    For Each tableInFile In tablesInFile.Rows
      currentTable = tableInFile.Item("TABLE_NAME").ToString
      excelTables(tableIndex) = currentTable
      tableIndex += 1
    Next

    Dts.Variables("ExcelTables").Value = excelTables

    Dts.TaskResult = Dts.Results.Success

代替ソリューション

スクリプトタスクを使用して Excel テーブルの一覧を配列に集める代わりに、ForEach ADO.NET Schema Rowset 列挙子を使用して Excel ワークブックファイル内のすべてのテーブル (つまり、ワークシートと名前付き範囲) を繰り返し処理することもできます。詳細については、「Excel のファイルおよびテーブルをループ処理する方法」を参照してください。

サンプルの結果の表示

このトピックの各例を同じパッケージで構成した場合は、すべてのスクリプトタスクを、すべての例の出力を表示する追加のスクリプトタスクに接続することができます。

このトピックの例の出力を表示するスクリプトタスクを構成するには

パッケージに新しいスクリプトタスクを追加し、名前を DisplayResults に変更します。
4 つのスクリプトタスク例のそれぞれを互いに接続し、各タスクが、前のタスクが正常に完了した後に実行されるようにして、4 番目のタスク例を DisplayResults タスクに接続します。
[スクリプトタスクエディタ] で DisplayResults タスクを開きます。
[スクリプト] タブで、「サンプルをテストするためのパッケージの構成」で示した 7 つの変数のすべてを ReadOnlyVariables の一覧に追加します。
[スクリプトのデザイン] をクリックして、スクリプトエディタを開きます。
スクリプトファイルの先頭に、Microsoft.VisualBasic 名前空間と System.Windows.Forms 名前空間の Imports ステートメントを追加します。
Sub Main 内のすべてのコードを、次のコードに置き換えます。
パッケージを実行し、メッセージボックスに表示される結果を調べます。

結果を表示するコード

    Const EOL As String = ControlChars.CrLf

    Dim results As String
    Dim filesInFolder As String()
    Dim fileInFolder As String
    Dim tablesInFile As String()
    Dim tableInFile As String

    results = _
      "Final values of variables:" & EOL & _
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _
      EOL

    results &= "Excel files in folder: " & EOL
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())
    For Each fileInFolder In filesInFolder
      results &= " " & fileInFolder & EOL
    Next
    results &= EOL

    results &= "Excel tables in file: " & EOL
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())
    For Each tableInFile In tablesInFile
      results &= " " & tableInFile & EOL
    Next

    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)

    Dts.TaskResult = Dts.Results.Success

参照

その他の技術情報

Excel 接続マネージャ
 Excel のファイルおよびテーブルをループ処理する方法

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

次の方法で共有

スクリプトタスクを使用した Excel ファイルの操作

サンプルをテストするためのパッケージの構成

このトピックの例で使用するパッケージを構成するには

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

このスクリプトタスクの例を構成するには

例 1 のコード

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

このスクリプトタスクの例を構成するには

例 2 のコード

例 3 の説明 : フォルダ内の Excel ファイルの一覧を取得する

このスクリプトタスクの例を構成するには

例 3 のコード

代替ソリューション

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

このスクリプトタスクの例を構成するには

例 4 のコード

代替ソリューション

サンプルの結果の表示

このトピックの例の出力を表示するスクリプトタスクを構成するには

結果を表示するコード

参照

その他の技術情報

ヘルプおよび情報

その他のリソース

次の方法で共有

スクリプト タスクを使用した Excel ファイルの操作

サンプルをテストするためのパッケージの構成

このトピックの例で使用するパッケージを構成するには

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

このスクリプト タスクの例を構成するには

例 1 のコード

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

このスクリプト タスクの例を構成するには

例 2 のコード

例 3 の説明 : フォルダ内の Excel ファイルの一覧を取得する

このスクリプト タスクの例を構成するには

例 3 のコード

代替ソリューション

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

このスクリプト タスクの例を構成するには

例 4 のコード

代替ソリューション

サンプルの結果の表示

このトピックの例の出力を表示するスクリプト タスクを構成するには

結果を表示するコード

参照

その他の技術情報

ヘルプおよび情報

その他のリソース

スクリプトタスクを使用した Excel ファイルの操作

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このスクリプトタスクの例を構成するには

このトピックの例の出力を表示するスクリプトタスクを構成するには