次の方法で共有

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

  • [アーティクル]

新規 : 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 ファイル内のテーブルの一覧を取得する

サンプルの結果の表示

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

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

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

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

  1. Business Intelligence Development Studio で新しい Integration Services プロジェクトを作成し、編集のために既定のパッケージを開きます。

  2. 変数。[変数] ウィンドウを開き、次の変数を定義します。

    • String 型の ExcelFile。既存の Excel ワークブックの完全なパスとファイル名を入力します。
    • String 型の ExcelTable。ExcelFile 変数の値で指定されたワークブック内の既存のワークシートまたは名前付き範囲の名前を入力します。この値では大文字と小文字が区別されます。
    • Boolean 型の ExcelFileExists。
    • Boolean 型の ExcelTableExists。
    • String 型の ExcelFolder。少なくとも 1 つの Excel ワークブックを含むフォルダの完全なパスを入力します。
    • Object 型の ExcelFiles。
    • Object 型の ExcelTables。

  3. Imports ステートメント。ほとんどのコード サンプルでは、スクリプト ファイルの先頭で次の .NET Framework 名前空間のいずれかまたは両方をインポートする必要があります。

    • ファイル システム操作用の System.IO。
    • Excel ファイルをデータ ソースとして開くための System.Data.OleDb。

  4. 参照。Excel ファイルからスキーマ情報を読み取るコード サンプルでは、スクリプト プロジェクトで System.Xml 名前空間への追加の参照が必要です。

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

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

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

  1. パッケージに新しいスクリプト タスクを追加し、名前を ExcelFileExists に変更します。

  2. [スクリプト タスク エディタ] を開きます。

  3. [スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFile を追加し、ReadWriteVariables の一覧に ExcelFileExists を追加します。

  4. [スクリプトのデザイン] をクリックして、スクリプト エディタを開きます。

  5. スクリプト ファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。

  6. 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 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

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

  1. パッケージに新しいスクリプト タスクを追加し、名前を ExcelTableExists に変更します。

  2. [スクリプト タスク エディタ] を開きます。

  3. [スクリプト] タブで、ReadOnlyVariables の一覧に ExcelTable と ExcelFile を追加し、ReadWriteVariables の一覧に ExcelTableExists を追加します。

  4. [スクリプトのデザイン] をクリックして、スクリプト エディタを開きます。

  5. スクリプト プロジェクトに System.Xml アセンブリへの参照を追加します。

  6. スクリプト ファイルの先頭に、System.IO 名前空間と System.Data.OleDb 名前空間の Imports ステートメントを追加します。

  7. 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 列挙子を使用して、配列内のファイルを繰り返し処理することができます。

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

  1. パッケージに新しいスクリプト タスクを追加し、名前を GetExcelFiles に変更します。

  2. [スクリプト タスク エディタ] を開きます。

  3. [スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFolder を追加し、ReadWriteVariables の一覧に ExcelFiles を追加します。

  4. [スクリプトのデザイン] をクリックして、スクリプト エディタを開きます。

  5. スクリプト ファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。

  6. 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 列挙子を使用して、配列内のテーブルを繰り返し処理することができます。

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

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

  1. パッケージに新しいスクリプト タスクを追加し、名前を GetExcelTables に変更します。

  2. [スクリプト タスク エディタ] を開きます。

  3. [スクリプト] タブで、ReadOnlyVariables の一覧に ExcelFile を追加し、ReadWriteVariables の一覧に ExcelTables を追加します。

  4. [スクリプトのデザイン] をクリックして、スクリプト エディタを開きます。

  5. スクリプト プロジェクトに System.Xml 名前空間への参照を追加します。

  6. スクリプト ファイルの先頭に、System.Data.OleDb 名前空間の Imports ステートメントを追加します。

  7. 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 のファイルおよびテーブルをループ処理する方法」を参照してください。

サンプルの結果の表示

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

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

  1. パッケージに新しいスクリプト タスクを追加し、名前を DisplayResults に変更します。

  2. 4 つのスクリプト タスク例のそれぞれを互いに接続し、各タスクが、前のタスクが正常に完了した後に実行されるようにして、4 番目のタスク例を DisplayResults タスクに接続します。

  3. [スクリプト タスク エディタ] で DisplayResults タスクを開きます。

  4. [スクリプト] タブで、「サンプルをテストするためのパッケージの構成」で示した 7 つの変数のすべてを ReadOnlyVariables の一覧に追加します。

  5. [スクリプトのデザイン] をクリックして、スクリプト エディタを開きます。

  6. スクリプト ファイルの先頭に、Microsoft.VisualBasic 名前空間と System.Windows.Forms 名前空間の Imports ステートメントを追加します。

  7. Sub Main 内のすべてのコードを、次のコードに置き換えます。

  8. パッケージを実行し、メッセージ ボックスに表示される結果を調べます。

結果を表示するコード

    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 の参考資料の入手