Ответ 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.
Надеюсь, что это поможет.