Импорт большого листа Excel в SQL
Ежемесячно мне дают электронные таблицы, которые мне нужно импортировать в SQL. Обычно я использую мастер импорта и экспорта SQL Server и импортирую его во временную таблицу, где я затем вызываю хранимые процедуры для завершения процесса импорта. Все это отлично работало на моем производственном сервере и работало нормально на моей машине для разработки, пока на прошлой неделе я не выполнил чистую установку Windows 10. Теперь я все еще могу импортировать небольшие таблицы с несколькими тысячами строк, однако если я пытаюсь импортировать данные из электронных таблиц, которые содержат более 500000 строк данных, это займет пару минут как для "Установки соединения с источником", так и для "Установки соединения с назначением", а затем будет зависать при "Копировании". Я проверил логи и запустил SQL Profiler и ничего не вижу относительно сбоев. Я позволил процессу работать более часа и, в конце концов, убил его, просмотрел временную таблицу и показал, что записи не были импортированы. Формат данных хорош, так как я могу импортировать на свой рабочий сервер без проблем.
Теперь вот что круче... Я могу импортировать эти большие листы на свой сервер разработки, только если у меня одновременно открыта электронная таблица в Excel (обратите внимание, что для небольших таблиц мне не нужно открывать электронную таблицу) импортировать). С открытой электронной таблицей процесс импорта работает, но он очень медленный и не эффективен.
Любые мысли о том, почему импорт работает только на небольших электронных таблицах, если электронная таблица также открыта в Excel?
Я использую SQL Server 2008 R2 в Windows 10 Pro и Office 2016. У меня также установлен AccessDatabaseEngine - 2007.
РЕДАКТИРОВАТЬ: я создал пакет служб SSIS и запустил его, как предложено, и все работало, как ожидалось. Чтобы сделать вещи еще более странными, я прошел через Мастер импорта / экспорта и выбрал "Немедленно запустить" и создать SSIS, и все снова заработало, как и ожидалось. Я снова прошел через мастер, но выбрал "Немедленно запустить" (не создавал SSIS), и он все еще зависает при копировании. Я попытался запустить его еще 4 раза, и каждый раз, когда он работает, выбирая "Немедленно запустить" и "Создать SSIS", но каждый раз не удается выбрать только "Немедленно запустить" (без создания служб SSIS). Я честно запутался, как это может произойти, и я открыт для новых предложений.
1 ответ
Наконец-то нашел решение этой проблемы!
Поскольку я был в состоянии импортировать листы в открытом состоянии, это заставило меня думать, что мои проблемы были связаны с медленной связью между SQL и Excel, поэтому я начал смотреть в этом направлении. На других сайтах я обнаружил несколько сообщений, в которых говорилось о медленных соединениях ODBC с Windows 8 и 10. На одном сайте упоминалось переключение драйвера на "Собственный клиент SQL". Я выбирал пункт назначения как "Собственный клиент SQL Server 10.0", я переключился на "Собственный клиент SQL", как было предложено, и теперь все работает как положено.
Я вижу эти два драйвера в списке:
Driver Version
------------------------------------------------
SQL Native Client 2005.90.3042.00
SQL Server Native Client 10.0 2009.100.6000.34
Я предполагаю, что более старый (2005) собственный клиент SQL работает потому, что при импорте / экспорте используется AccessDatabaseEngine - 2007.
ПРИМЕЧАНИЕ. При использовании Windows 7 или 2008 Server у меня не было проблем с импортом больших таблиц с помощью (2009) собственного клиента SQL Server 10.0. Похоже, что это проблема только с более поздними версиями Windows. Я видел несколько других сайтов, на которых упоминалось, что были проблемы с производительностью при использовании TCP для их соединений ODBC в Windows 10, и они заметили заметное улучшение, используя вместо этого именованные каналы. Поскольку мой производственный сервер использует TCP, я не вносил изменения в Named Pipes на моей машине для разработки, поэтому я не могу это подтвердить, но хотел упомянуть об этом, если кто-то сталкивается с этим сообщением, имеющим такую проблему.