この記事では、SQL Server Integration Services (SSIS) における Excel 接続マネージャーの設定方法について説明しています。Excel 接続マネージャーをパッケージに追加すると、実行時に Excel 接続として解決され、プロパティが設定されます。設定には、Excel ワークブックのパス、使用された Excel のバージョン、最初の行にカラム名が含まれているかどうかを指定できます。混合データ型のデータをインポートする場合、デフォルトでは Excel ドライバーが最初の 8 行を基にデータ型を推測し、この場合、テキスト値のインポートがNULLになります。解決策として、Excel カラムをテキスト型に変更すること、または接続文字列に「;IMEX=1」を追加する方法があります。

Excel接続マネージャーの設定と利用方法

概要

Microsoft SQL Server Integration Services (SSIS) を使用することで、Excelワークブックファイルに接続するためのExcel接続マネージャーを設定できます。Excel接続マネージャーは、データフローのソースや宛先としてExcelを使用する際に必要不可欠なコンポーネントです。

Excel接続マネージャーの構成

Excel接続マネージャーは、以下の方法で設定できます。

1. Excelワークブックファイルのパスを指定:
   接続マネージャーの設定で、対象のExcelファイルのパスを指定します。

2. Excelのバージョンを指定:
   使用するExcelファイルが作成されたExcelのバージョンを選択します。

3. 最初の行に列名が含まれているかを示す:
   選択したワークシートの最初の行に列名が含まれているかどうかを設定します。デフォルトではこのオプションは「True」に設定されています。

これらのプロパティは、SSIS Designerを通じて、またはプログラム的に設定することができます。

SSIS Designerでの設定

SSIS Designerを使用すると、Excel接続マネージャーのプロパティを簡単に設定できます。詳細については、Excel接続マネージャーエディターを参照してください。

プログラムによる接続マネージャーの設定

接続マネージャーをプログラム的に構成するための情報は、ConnectionManagerや、プログラム的に接続を追加する方法を参照してください。

Excel接続マネージャーエディター

Excel接続マネージャーエディターを使用して、既存のExcelワークブックファイルへの接続を追加したり、新しいファイルを作成したりできます。

設定オプション

• Excelファイルパス:
  Excelワークブックファイルのパスとファイル名を入力します。

• 参照:
  「開く」ダイアログを使用して、Excelファイルが存在するフォルダーや新しいファイルを作成する場所を参照します。

• Excelのバージョン:
  ファイルが作成されたMicrosoft Excelのバージョンを指定します。

• 最初の行に列名があるか:
  選択したワークシートの最初の行に列名が含まれているか否かを指定します。デフォルトは「True」です。

Excelからの混合データタイプのインポート問題

Excelに含まれる混合データタイプのデータを扱う際、Excelドライバーは初めに最初の8行を読み取り、それに基づいて各列のデータタイプを推測します。このため、特定の条件下では文字列がNULLとして扱われることがあります。これを避けるための解決策は以下の通りです。

1. Excelファイル内の列の型を「テキスト」に変更する。
2. 接続文字列にIMEX拡張プロパティを追加する。「;IMEX=1」を接続文字列の末尾に付け加え、全データをテキストとして扱うようにします。例:

   Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFileName.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";

3. 必要に応じて、レジストリ設定を変更します。
4. ファイルをCSV形式で保存し、SSISパッケージをCSVインポートに対応させる。

さらなる情報

以下はExcelにデータをロードするための役立つリンクです：

• ExcelからSQL Server Integration Servicesでデータを読み込む
• Excelソース
• Excel宛先

Excel接続マネージャーの効果的な利用は、データのインポートやエクスポートをスムーズに行うための鍵となります。これらの設定を活用して、効率的なデータ操作を実現してください。

————-

Excel Connection Manager – SQL Server Integration Services (SSIS)

Source link

The article discusses the use of the Excel Connection Manager in SQL Server Integration Services (SSIS), which allows for the connection to Microsoft Excel workbook files. This connection manager is essential for transferring data between Excel and other data sources within SSIS.

Key points include:

1. Configuration: Users can configure the Excel connection manager by specifying the file path, Excel version, and indicating if the first row contains column names. This can be done through SSIS Designer or programmatically.

2. Excel Connection Manager Editor: This tool facilitates the creation of connections to existing or new Excel files, allowing users to browse for files and specify the necessary parameters.

3. Handling Mixed Data Types: The article addresses challenges when importing data with mixed types. By default, the Excel driver analyzes the first 8 rows to determine data types, which can result in text values being skipped if the majority of the initial rows are numeric. Suggestions to resolve this include changing the column type in Excel or adding an "IMEX=1" property to the connection string to treat all values as text.

4. Registry Modifications: For reliable performance regarding data type handling, users may need to modify registry settings to configure the TypeGuessRows setting, which influences the number of rows used to predict data types.

5. Alternative Solutions: If issues persist, saving the Excel file as a CSV and configuring the SSIS package for CSV import can be an effective workaround.

Overall, the article provides a comprehensive guide on how to effectively use the Excel connection manager in SSIS for data import and export processes, along with best practices for handling potential data type inconsistencies.