Импорт плоских файлов в SQL Server может быть неприятным опытом, особенно когда формат файлов внезапно меняется в процессе работы. Большинство интеграционных инструментов, таких как SSIS, сильно полагаются на постоянные и фиксированные метаданные для работы с плоскими файлами. Однако существует альтернативное решение, которое может обрабатывать импорт плоских файлов с изменяющимися метаданными в структурированном и надежном способе.

При импорте плоских файлов в SQL Server стандартные инструменты интеграции требуют фиксированные метаданные из файлов для сопоставления каждого столбца из источника в назначение. Если метаданные изменяются, процесс импорта может сломаться или давать неправильные результаты.

Например, предположим, у нас есть таблица исходного плоского файла с колонками для имени, пола, возраста, города и страны. Мы можем импортировать этот файл в базу данных SQL Server с помощью следующего скрипта:

CREATE TABLE dbo.personlist (
    [name] VARCHAR(20),
    [gender] VARCHAR(10),
    [age] INT,
    [city] VARCHAR(20),
    [country] VARCHAR(20)
);

BULK INSERT dbo.personlist FROM 'c:\source\personlist.csv' WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n',
    TABLOCK,
    CODEPAGE = 'ACP'
);

SELECT * FROM dbo.personlist;

Если столбец ‘Country’ удаляется из файла после настройки импорта, процесс импорта либо сломается, либо даст неправильные результаты. Метаданные файла изменились.

Для обработки изменяющихся метаданных при импорте плоских файлов мы можем использовать функциональность OPENROWSET в TSQL с форматным файлом. Форматный файл – это простой XML-файл, который содержит информацию о структуре исходного файла, включая столбцы, типы данных, разделитель строк и сортировку.

Чтобы автоматически сгенерировать начальный форматный файл, вы можете использовать .NET консольное приложение. Это приложение принимает входные данные, такие как путь к исходному файлу, имя форматного файла и разделитель полей. Оно генерирует форматный файл на основе структуры исходного файла.

После генерации форматного файла вы можете использовать его в скриптах TSQL с OPENROWSET для импорта данных из плоского файла. Вот пример скрипта:

-- Импорт файла с использованием форматного файла
SELECT * INTO dbo.personlist_bulk FROM OPENROWSET(
    BULK 'c:\source\personlist.csv',
    FORMATFILE = 'c:\source\personlistformatfile.xml',
    FIRSTROW = 2
) AS t;

-- Загрузка данных из personlist_bulk в personlist
TRUNCATE TABLE dbo.personlist;
INSERT INTO dbo.personlist (name, gender, age, city, country)
SELECT * FROM dbo.personlist_bulk;

SELECT * FROM dbo.personlist;

DROP TABLE dbo.personlist_bulk;

Этот скрипт загружает данные из исходного файла в новую таблицу с именем ‘personlist_bulk’. Затем вы можете легко загрузить данные из ‘personlist_bulk’ в таблицу назначения ‘personlist’.

Для обработки динамических изменений в исходном файле, таких как изменения ширины столбцов или порядка столбцов, вы можете использовать динамический подход для загрузки данных из таблицы bulk в таблицу назначения. Это включает генерацию списка имен столбцов из исходной таблицы, которые соответствуют именам столбцов в таблице назначения.

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

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

Реализуя это решение, вы можете обрабатывать изменяющиеся метаданные при импорте плоских файлов в более структурированном и управляемом способе. Для настройки начальной генерации форматного файла может потребоваться некоторые навыки программирования на .NET, но после его создания вы можете повторно использовать это приложение в различных интеграционных решениях в вашей среде.

Счастливого кодирования!