Foreach ループ コンテナーを使用して Excel のファイルおよびテーブルをループ処理する方法

ループの反復ごとに現在の Excel のパスとファイル名を受け取る文字列変数を作成します。検証で問題が発生しないようにするには、変数の初期値として Excel の有効なパスとファイル名を割り当てます (この手順の後半で示すサンプル式では、ExcelFile という変数名を使用します)。

必要に応じて、Excel 接続文字列の Extended Properties 引数の値を保持する別の文字列変数を作成します。この引数には、Excel のバージョンを指定したり、最初の行に列名が含まれているかどうか、およびインポート モードが使用されるかどうかを判断する一連の値が格納されます (この手順の後半で示すサンプル式では、初期値が "Excel 8.0;HDR=Yes" である ExtProperties という名前の変数を使用します)。

Extended Properties 引数の値を格納している変数を使用しない場合は、接続文字列を含む式に引数の値を手動で追加する必要があります。

Foreach ループ コンテナーを [制御フロー] タブに追加します。Foreach ループ コンテナーの構成方法については、「Foreach ループ コンテナの構成方法」を参照してください。

[Foreach ループ エディター] の [コレクション] ページで [Foreach File 列挙子] を選択し、Excel ブックが存在するフォルダーを指定して、ファイル フィルター (通常は *.xls) を指定します。

[変数のマッピング] ページで、ループの反復ごとに現在の Excel のパスとファイル名を受け取るユーザー定義文字列変数に、インデックス 0 をマップします (この手順の後半で示すサンプル式では、ExcelFile という変数名を使用します)。

[Foreach ループ エディター] を閉じます。

「パッケージの接続マネージャーを追加または削除する方法」の説明に従って、Excel 接続マネージャーをパッケージに追加します。接続時に検証エラーが発生しないように、既存の Excel ブック ファイルを選択してください。

重要

この Excel 接続マネージャーを使用するデータ フロー コンポーネントとタスクを構成する際、検証エラーが発生しないようにするには、[Excel 接続マネージャー] で既存の Excel ブックを選択します。以下の手順に従って ConnectionString プロパティ用の式を構成した後は、接続マネージャーは実行時にこのブックを使用しなくなります。パッケージを作成して構成したら、[プロパティ] ウィンドウで ConnectionString プロパティの値を削除することができます。ただし、この値を削除すると、Excel 接続マネージャーの接続文字列プロパティは Foreach ループが実行されるまで無効になります。したがって、接続マネージャーを使用するタスクまたはパッケージでは、検証エラーが発生しないように、DelayValidation プロパティを True に設定してください。 また、Excel 接続マネージャーの RetainSameConnection プロパティでは既定値の False を使用する必要があります。この値を True に変更すると、ループの各反復処理で最初の Excel ブックが繰り返し開かれるようになります。

新しい Excel 接続マネージャーを選択し、[プロパティ] ウィンドウで [式] プロパティをクリックして、参照ボタンをクリックします。

[プロパティ式エディター] で、[ConnectionString] プロパティを選択し、参照ボタンをクリックします。

式ビルダーで、次の式を入力します。

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

Extended Properties 引数の値の前後に必要な内側の引用符をエスケープするために、エスケープ文字 "\" を使用していることに注意してください。

Extended Properties 引数は省略できません。引数の値を格納している変数を使用しない場合は、Excel 2003 ファイルの次の例に示すように、式に引数の値を手動で追加する必要があります。

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"

Foreach ループ コンテナー内で、Excel 接続マネージャーを使用して、指定したファイルの場所とパターンに一致した各 Excel ブックに対して同じ操作を実行するタスクを作成します。

Microsoft Jet OLE DB Provider を使用して Excel ブックに接続する ADO.NET 接続マネージャーを作成します。[接続マネージャー] ダイアログ ボックスの [すべて] ページで、Extended Properties プロパティの値として「Excel 8.0」と入力します。詳細については、「パッケージの接続マネージャーを追加または削除する方法」を参照してください。

ループの反復ごとに現在のテーブルの名前を受け取る文字列変数を作成します。

Foreach ループ コンテナーを [制御フロー] タブに追加します。Foreach ループ コンテナーの構成方法については、「Foreach ループ コンテナの構成方法」を参照してください。

[Foreach ループ エディター] の [コレクション] ページで、Foreach ADO.NET Schema Rowset 列挙子を選択します。

[コレクション] の値として、以前に作成した ADO.NET 接続マネージャーを選択します。

[スキーマ] の値として、[テーブル] を選択します。

注
Excel ブック内のテーブルの一覧には、ワークシート ($ サフィックスが付きます) と名前付き範囲が含まれます。ワークシートのみ、または名前付き範囲のみを一覧からフィルター選択する場合は、そのためのカスタム コードをスクリプト タスクで記述する必要があります。詳細については、「スクリプト タスクを使用した Excel ファイルの操作」を参照してください。

[変数のマッピング] ページで、以前に作成した文字列変数にインデックス 2 をマップし、現在のテーブルの名前を保持します。

[Foreach ループ エディター] を閉じます。

Foreach ループ コンテナー内で、Excel 接続マネージャーを使用して、指定したブック内の各 Excel テーブルに対して同じ操作を実行するタスクを作成します。スクリプト タスクを使用して、列挙されるテーブル名を調べたり各テーブルを操作したりする場合、スクリプト タスクの ReadOnlyVariables プロパティに文字列変数を追加することを忘れないでください。