Проблема с Excel Data Source в SSIS

Недавно пришлось автоматизировать загрузку Excel файлов в базу с последующим обновлением OLAP куба. Неожиданно Excel Data Source преподнес один неприятный сюрприз — некоторые колонки с кодами (например, CF-15) были интерпретированы как числовые, в результате чего все смешанные данные были преобразованы в null. Пошерстив в интернете вычитал, что:

  1. По-умолчанию Excel сканирует 8 первых строк таблицы и на их основе определяет тип данных;
  2. Тип данных определяется на основе большинства(!) из вышеупомянутых 8 строк, т.е. если в нашей колонке будут значения А, А1, 2, 3, 4, 5, 6, 7 то тип колонки станет числовой а не текстовый и в настройке SSIS с этим ничего не поделаешь 😦
Логика здесь просто убийственная, чесно говоря не понимаю как до такого можно было вообще додуматься и кому такое может быть нужно. Иначе как халтурой со стороны Microsoft не назовешь.
В результате анализ показал, что проблема кроется в не столько в SSIS сколько в JET драйвере Excel. Чтобы изменить поведение и заставить драйвер приводить все смешанные данные к тексту необходимо изменить строку подключения, добавив IMEX=1:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile1.xls;Extended Properties=»EXCEL 8.0;HDR=YES;IMEX=1«;

Это переведет работу драйвера в режим импорта и, если при анализе столбца он встретит в первых 8 ячейках смешанные данные то преобразует столбец в текст. Проблема кроется в том, что первые 8 строк у вас могут содержать числа, в то время как в последующих встретится строка. Это лечится при помощи ключа реестра TypeGuessRows в котором число семпла необходимо увеличить с 8 до максимально возможного:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Excel

 

 

 

 

 

 

 

 

 

Реклама
Запись опубликована в рубрике SQL Server, SQL Server 2008, SQL Server Integration Services. Добавьте в закладки постоянную ссылку.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s