2.2.5 Bulk Insert Task

Bulk Insert Task в DTS (Data Transformation Services) в SQL Server 2000, массовая загрузка данных на SQL Server, файл форматирования (format file)

Bulk Insert Task - самый быстрый способ загрузить данные на SQL Server (в таблицу напрямую или через представление). Обеспечивает графический интерфейс для команды Bulk Insert в Transact-SQL, использует специальный программный объект BulkInsertTask (некоторые свойства этого объекта на графическом интерфейсе настроить нельзя - доступны только программным образом).

В отличие от утилиты BCP, которая использует те же программные объекты, при помощи Bulk Insert Task выгрузить данные из SQL Server нельзя - только загрузка.

Когда применять:

1) когда важнее всего скорость загрузки данных (работает намного быстрее, чем задания, использующие объект Data Pump);

2) когда нужно параллельно очень быстро произвести преобразования из одних кодировок (например, при закачке данных из DBF с кодировками DOS на SQL Server - вначале выгрузить в текстовый файл, потом преобразовать утилитой командной строки в другую кодировку, потом загрузить при помощи Bulk Insert Task).

Для настройки Bulk Insert Task необходимо создать в пакете хотя бы одно соединение с SQL Server. Большинство возможностей Bulk Insert Task очевидны (вкладки General и Options), однако некоторые очень важные возможности доступны только через файл форматирования и на графическом интерфейсе недоступны.

Что можно сделать через файл форматирования:

1) выбрать для каждого столбца свою собственную кодировку;

2) вставить в таблицу SQL Server не все столбцы, а лишь некоторые из тех, которые есть в текстовом файле;

3) поменять местами столбцы при загрузке;

4) указать для каждого столбца свой собственный разделитель.

В простых ситуациях можно воспользоваться кнопкой Generate в свойствах Bulk Insert Task, в более сложных - необходимо править файл форматирования, написав его полностью вручную или сгенерировав его средствами BCP (почему-то работает некорректно).

Предположим, что у нас есть текстовый файл для загрузки, в котором содержатся лишние столбцы. В этом случае воспользоваться кнопкой Generate не удастся - при сравнении таблицы назначения и текстового файла DTS Designer определит, что наборы столбцов разные. Поэтому, чтобы сгенерировать файл форматирования автоматически, действуем так:

1) создаем временную таблицу, которая полностью соответствует формату текстового файла (например, при помощи Transform Data Task, которую выполнять вовсе не обязательно, или выполнить на 5-10 строк)

2) создаем Bulk Insert Task для закачки данных в эту таблицу. Поскольку наборы столбцов теперь совпадают, то кнопка Generate теперь работает.

3) генерируем файл форматирования и открываем его в блокноте. Структура его выглядит так:

·        Version - версия утилиты BCP (соответствует версии SQL Server);

·        Number of Fields - количество полей в текстовом файле - источнике, обычно соответствует количеству столбцов в таблице назначения;

·        Далее - описание для каждого поля в текстовом файле:

·        Host File field order - номер поля в текстовом файле, начинается с единицы;

·        Host File Data Type - тип вставляемых данных;

·        Prefix Length - длина текстового поля. В BCP используется только для экспорта данных из SQL Server, для целей импорта (как у нас) всегда ставится 0;

·        Host File data Length - максимальная длина значения в текстовом поле;

·        Terminator - разделитель данных в текстовом файле;

·        Server Column Order - номер столбца в таблице назначения.

·        Server Column Name - имя столбца в таблице назначения на SQL Server (реально Bulk Insert Task его игнорирует, используя только порядок столбцов).

Главный столбец, на который мы ориентируемся - Server Column Order. При помощи него можно поменять местами столбцы, а если его поставить в 0 - то этот столбец на источник вставляться не будет. Поэтому наша задача - для всех лишних столбцов поставить это значение в 0.

После этого полученный файл форматирования можно использовать в Bulk Insert Task.

Справка по некоторым параметрам настройки Bulk Insert Task на вкладке Options:

·        Check Constraints - проверять или нет на соответствие ограничениям, которые наложены на таблицу назначения. По умолчанию - не проверять.

·        Enable Identity Insert - разрешить вставку данных в столбец счетчика;

·        Sorted Data - сортировка вставляемых данных по указанному в поле столбцу. Должно быть точно введено название соответствующего столбца;

·        Keep Nulls - что будет, если в таблице назначения есть столбец, для которого настроено значение по умолчанию: если флажок стоит, но значения по умолчанию не будут использованы (будут вставляться NULL);

·        Table Lock - будет ли использована блокировка уровня таблицы при операции массовой вставки данных (по умолчанию - блокировка на уровне записи);

·        Code Page - кодовая страница для символов с кодом больше 127;

·        Data Type File - тип файла в соответствии с градациями программы bcp;

·        Insert Batch Size - размер пакета при вставке. Если равно нулю (по умолчанию), то вся вставка данных расценивается как единый пакет, и при невозможности вставки одной строки отменяется вставка всего файла. Если равно 1 или другое число, то операция вставки делится на пакеты (размером 1 или сколько укажем строк). При невозможности вставки одной строки отменяется вставка только данного пакета, но не всего файла.

·        Only Copy Selected Rows - указать диапазон записей для вставки (с какой по какую строку в файле вставлять).

Получить учебные материалы по этому курсу