Поделиться через

Диспетчер подключений Excel

  • Статья

Область применения: среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure

Диспетчер соединений Excel позволяет пакету подключаться к файлу книги Microsoft Excel. Источник Excel и назначение Excel, включаемые службами Microsoft SQL Server Integration Services, используют диспетчер соединений Excel.

Внимание

Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).

При добавлении диспетчера соединений Excel в пакет службы Integration Services создает диспетчер соединений, который разрешается в качестве подключения Excel во время выполнения, задает свойства диспетчера соединений и добавляет диспетчер соединений в коллекцию "Подключения " в пакете.

Свойству ConnectionManagerType диспетчера соединений присваивается значение EXCEL.

Настройка диспетчера соединений Excel

Чтобы настроить диспетчер соединений Excel, выполните следующее:

  • Укажите путь файла рабочей книги Excel.

  • Укажите версию приложения Excel, использовавшуюся при создании файла.

  • Укажите, содержатся ли имена столбцов в первой строке выбранного рабочего листа или диапазона.

Свойства могут быть заданы с помощью конструктора SSIS или программным путем.

Дополнительные сведения о свойствах, которые можно задать в конструкторе служб SSIS, см. в редакторе диспетчер подключений Excel.

Дополнительные сведения о программной настройке диспетчера подключений см. в разделах ConnectionManager и Добавление соединений программным образом.

Редактор диспетчера подключений Excel

Диалоговое окно "Редактор диспетчер подключений Excel" позволяет добавить подключение к существующему или новому файлу книги Microsoft Excel.

Параметры

Путь к файлу Excel
Введите путь и имя существующего или нового файла книги Excel.

Обзор
Для перехода в папку, в которой существует файл Excel или в которой будет создан новый файл, используйте диалоговое окно Открыть.

Версия Excel
Позволяет указать версию Microsoft Excel, в которой был создан файл.

Первая строка содержит имена столбцов
Позволяет указать, содержит ли первая строка данных выбранного листа имена столбцов. По умолчанию значение этого параметра равно True.

Решение для импорта смешанных типов данных из Excel

Если используются данные, содержащие смешанные типы данных, то по умолчанию драйвер Excel считывает первые 8 строк (настраивается ключом реестра TypeGuessRows). В зависимости от первых 8 строк данных драйвер Excel пытается распознать тип данных каждого столбца. Например, если источник данных Excel содержит числа и текст в одном столбце, а первые 8 строк содержат числа, то драйвер может на основе первых 8 строк определить, что данные в столбце являются целочисленными. В этом случае службы SSIS пропускают текстовые значения и импортируют их в назначение как значения NULL.

Чтобы устранить эту проблему, можно попробовать одно из следующих решений.

  • Измените тип столбца Excel на Текстовый в файле Excel.

  • Добавьте расширенное свойство IMEX в строку подключения, чтобы переопределить поведение драйвера по умолчанию. При добавлении расширенного свойства ";IMEX=1" в конец строки подключения Excel обрабатывает все данные как текст. См. следующий пример.

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

    Для надежной работы этого решения может также потребоваться изменить параметры реестра. Файл main.cmd выглядит следующим образом:

    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

  • Сохраните файл в формате CSV и активируйте в пакете служб SSIS поддержку импорта CSV.

Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS)
Источник Excel
Назначение «Excel»