Muokkaa

Jaa

Excel Connection Manager

  • Artikkeli

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

An Excel connection manager enables a package to connect to a Microsoft Excel workbook file. The Excel source and the Excel destination that Microsoft SQL Server Integration Services includes use the Excel connection manager.

Important

For detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see Load data from or to Excel with SQL Server Integration Services (SSIS).

When you add an Excel connection manager to a package, Integration Services creates a connection manager that is resolved as an Excel connection at run time, sets the connection manager properties, and adds the connection manager to the Connections collection on the package.

The ConnectionManagerType property of the connection manager is set to EXCEL.

Configure the Excel Connection Manager

You can configure the Excel connection manager in the following ways:

  • Specify the path of the Excel workbook file.

  • Specify the version of Excel that was used to create the file.

  • Indicate whether the first row in the selected worksheets or ranges contains column names.

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, see Excel Connection Manager Editor.

For information about configuring a connection manager programmatically, see ConnectionManager and Adding Connections Programmatically.

Excel Connection Manager Editor

Use the Excel Connection Manager Editor dialog box to add a connection to an existing or a new Microsoft Excel workbook file.

Options

Excel file path
Type the path and file name of an existing or a new Excel workbook file.

Browse
Use the Open dialog box to navigate to the folder in which the Excel file exists or where you want to create the new file.

Excel version
Specify the version of Microsoft Excel that was used to create the file.

First row has column names
Specify whether the first row of data in the selected worksheet contains column names. The default value of this option is True.

Solution to import data with mixed data types from Excel

If you use data that contains mixed data types, by default, the Excel driver reads the first 8 rows (configured by the TypeGuessRows register key). Based on the first 8 rows of data, the Excel driver tries to guess the data type of each column. For example, if your Excel data source has numbers and text in one column, if the first 8 rows contain numbers, the driver might determine based on those first 8 rows that the data in the column is the integer type. In this case, SSIS skips text values and imports them as NULL into the destination.

To resolve this issue, you can try one of following solutions:

  • Change the Excel column type to Text in the Excel file.

  • Add the IMEX extended property to the connection string to override the driver's default behavior. When you add the ";IMEX=1" extended property to the end of the connection string, Excel treats all data as text. See the following example:

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

    For this solution to work reliably, you might have to also modify the registry settings. The main.cmd file is as follows:

    reg add "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel" /t REG_DWORD /v TypeGuessRows /d 0 /f

  • Save the file in CSV format and change the SSIS package to support a CSV import.

Load data from or to Excel with SQL Server Integration Services (SSIS)
Excel Source
Excel Destination