Загрузка файла Excel с изменяющимся количеством столбцов с использованием SSIS

Вопрос или проблема

Ответ или решение

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

Шаг 1: Настройка SSIS пакета

1. Создание нового SSIS пакета: Откройте SQL Server Data Tools (SSDT) и создайте новый SSIS проект.

2. Добавление подключения к Excel:

   • Используйте компонент Excel Source для подключения к вашему Excel файлу.
   • При настройке соединения выберите ваш Excel файл, а также настройте версию Excel.

3. Динамическое получение метаданных:

   • Чтобы работать с изменяющимся числом столбцов, можно настроить динамическое получение метаданных. Для этого вам потребуется выполнить SQL запрос для получения имен столбцов из вашей Excel таблицы.
   • Добавьте Script Task для выполнения кода на C#. Этот скрипт подключится к файлу Excel и получит список имен столбцов, используя библиотеки, такие как Microsoft.Office.Interop.Excel или EPPlus.

Шаг 2: Использование скриптов для обработки данных

1. Обработка данных в C#:
   • В скрипте вам нужно будет открывать Excel файл, получать данные и формировать динамическую структуру данных с помощью DataTable.
   • Создайте DataColumn для каждого уникального значения из заголовков, за исключением первого (EmpID).

using System.Data;
using System.Data.OleDb;
using ExcelDataReader;

// Пример кода для получения данных
public void Main()
{
    string filePath = Dts.Variables["User::ExcelFilePath"].Value.ToString();
    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            var result = reader.AsDataSet();
            DataTable excelData = result.Tables[0];

            // Обработка строк и подготовка данных для выходного формата
        }
    }
}

2. Сам процесс Unpivot:
   • Создайте новую таблицу или DataTable, чтобы сохранить результаты «разворачивания». Пройдитесь по каждой строке исходной таблицы и добавьте данные в новую таблицу.
   • Пример процесса разворачивания:

     foreach (DataRow row in excelData.Rows)
     {
     string empID = row[0].ToString();
     for (int i = 1; i < excelData.Columns.Count; i++)
     {
     string date = excelData.Columns[i].ColumnName;
     string value = row[i].ToString();
     // Добавление в новую таблицу (или другие структуры данных)
     }
     }

Шаг 3: Загрузка данных в базу данных

1. Использование ADO.NET:
   • После получения всех данных в нужном формате создайте OleDbConnection или SqlConnection для загрузки данных в вашу целевую базу данных.
   • Используйте SqlBulkCopy для быстрой загрузки данных в SQL Server или просто выполните поэлементную вставку через INSERT.

Заключение

Этот подход позволяет создать адаптивное и автоматизированное решение для загрузки Excel файлов с варьирующимся числом столбцов и последующим разворачиванием данных. Использование Script Task и динамического извлечения метаданных делает вашу работу намного более гибкой, что позволяет легко адаптироваться к изменениям в формате файла.

С помощью вышеперечисленного решения вы сможете соответствовать потребностям вашего бизнеса, обеспечивая высокую скорость и точность обработки данных.