Как перебрать файлы Excel и загрузить их в базу данных с помощью пакета SSIS?

Мне нужно создать пакет SSIS для импорта данных из нескольких файлов Excel в базу данных SQL. Я планирую использовать вложенные контейнеры Foreach Loop для достижения этого. Один перечислитель файлов Foreach и вложенный в него, перечислитель строк ADO.net для начинающих

Проблема: имена файлов различаются между файлами excel, но структура остается неизменной.

Я создал диспетчер подключений Excel, но перечислитель строк схемы не принимает диспетчер подключений в конфигурации Enumerator.

После исследования я обнаружил, что вы можете использовать поставщика Jet Ole db для подключения к файлу excel. Однако я могу указать только файлы базы данных Microsoft Access в качестве источника данных. Попытка вставить файл Excel в момент сбоя источника данных

После нескольких исследований я обнаружил, что вы можете использовать поставщик данных Odbc со строкой соединения вместо DSN. После вставки строки подключения, указывающей файл Excel, это также не удалось

Мне сказали не использовать задачу Script для выполнения этой задачи, и даже после попытки последней попытки извлечь данные из листов, чтобы получить доступ к листам по индексу, я обнаружил, что индекс для листов в разных файлах excel разные

Любая помощь будет принята с благодарностью

Ответ 1

Вот один из возможных способов сделать это, исходя из предположения, что в файлах Excel не будет никаких пустых листов, а также все листы будут следовать той же структуре. Кроме того, в предположении, что расширение файла только .xlsx

Следующий пример был создан с использованием SSIS 2008 R2 и Excel 2007. Рабочая папка для этого примера F:\Temp\

В пути к папке F:\Temp\ создайте файл электронной таблицы Excel 2007 с именем States_1.xlsx с двумя рабочими листами.

Sheet 1 of States1.xlsx содержит следующие данные

Sheet 2 of States1.xlsx содержит следующие данные

В пути к папке F:\Temp\ создайте другой файл таблицы Excel 2007 с именем States_2.xlsx с двумя рабочими листами.

Sheet 1 of States2.xlsx содержит следующие данные

Sheet 2 of States2.xlsx содержит следующие данные

Создайте таблицу в SQL Server с именем dbo.Destination с помощью ниже создайте script. Данные таблицы Excel будут вставлены в эту таблицу.

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [State] [nvarchar](255) NULL,
    [Country] [nvarchar](255) NULL,
    [FilePath] [nvarchar](255) NULL,
    [SheetName] [nvarchar](255) NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

В настоящее время таблица пуста.

Создайте новый пакет SSIS и пакет, создайте следующие 4 переменные. FolderPath будет содержать папку, в которой хранятся файлы Excel. FilePattern будет содержать расширение файлов, которые будут зацикливаться, и этот пример работает только для .xlsx. FilePath будет присвоено значение контейнером Foreach Loop, но для начала нужно использовать допустимый путь, и в настоящее время он заполняется с помощью пути F:\Temp\States_1.xlsx первого файла Excel. SheetName будет содержать фактическое имя листа, но нам нужно заполнить начальное значение Sheet1$, чтобы избежать ошибки времени разработки.

В диспетчере соединений пакетов создайте соединение ADO.NET со следующей конфигурацией и назовите его как ExcelSchema.

Выберите поставщика Microsoft Office 12.0 Access Database Engine OLE DB Provider в .Net Providers для OleDb. Укажите путь к файлу F:\Temp\States_1.xlsx

Нажмите на раздел All с левой стороны и установите для свойства Extended Properties значение Excel 12.0 для обозначения версии Excel. Здесь в этом случае 12.0 обозначает Excel 2007. Нажмите "Проверить соединение", чтобы убедиться, что соединение выполнено успешно.

Создайте диспетчер соединений Excel с именем Excel, как показано ниже.

Создайте SQL-соединение OLE DB с именем SQLServer. Итак, у нас должно быть три соединения на упаковке, как показано ниже.

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

В соединении ExcelSchema настройте выражение ServerName для использования переменной FilePath. Нажмите кнопку многоточия, чтобы настроить выражение.

Аналогично при подключении Excel настройте выражение ServerName для использования переменной FilePath. Нажмите кнопку многоточия, чтобы настроить выражение.

<Т411 >

В потоке управления поместите два контейнера Foreach Loop один в другой. Первые Foreach Loop container с именем Loop файлы будут проходить через файлы. Второй Foreach Loop container будет проходить через листы внутри контейнера. Внутри внутреннего Для каждого контейнера цикла поместите задачу потока данных, которая будет считывать файлы Excel и загружать данные в SQL

Настройте первый контейнер цикла Foreach с именем Loop files, как показано ниже:

Настройте первый контейнер цикла Foreach с именем Листы Loop, как показано ниже:

Внутри задачи потока данных поместите источник Excel, производный столбец и назначение OLE DB, как показано ниже:

Настройте источник Excel для чтения соответствующего файла Excel и листа, который в данный момент прокручивается.

Настройте производный столбец для создания новых столбцов для имени файла и имени листа. Это просто, чтобы продемонстрировать этот пример, но не имеет никакого значения.

Настроить назначение OLE DB для вставки данных в таблицу SQL.

Ниже снимок экрана показывает успешное выполнение пакета.

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

Надеюсь, что это поможет.

Ответ 2

Я столкнулся с статьей, которая иллюстрирует метод, в котором данные из одного и того же листа excel могут быть импортированы в выбранную таблицу до тех пор, пока не будет изменений в excel с типами данных.

Если данные вставляются или перезаписываются новыми, процесс импорта будет успешно выполнен, и данные будут добавлены в таблицу в базе данных SQL.

Статья может быть найдена здесь: http://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/

Надеюсь, что это поможет.

Ответ 3

У меня была аналогичная проблема, и я обнаружил, что было намного проще избавиться от файлов Excel как можно скорее. В рамках первых шагов моего пакета я использовал Powershell для извлечения данных из файлов Excel в CSV файлы. Мои собственные файлы Excel были простыми, но здесь

Извлечение и преобразование всех рабочих таблиц Excel в файлы CSV с помощью PowerShell

- отличная статья Тима Смита об извлечении данных из нескольких файлов Excel и/или нескольких листов.

Как только файлы Excel были преобразованы в CSV, импорт данных намного менее сложный.