Как перенести SSIS пакеты с одного сервера на другой (package deployment model)

В предыдущем посте я рассказывал о том, как делать массовую загрузку SSIS пакетов в хранилище. Сегодня расскажу, как скопировать пакеты с одного сервера на другой. Например, у вас есть сервер QA, на который вы загрузили все ваши пакеты SSIS, а теперь нужно сделать тоже самое на основном сервере (PRD). При небольшом количестве пакетов можно это сделать стандартными средствами, ну а для массового копирования я предлагаю использовать скрипт, который будет запускать утилиту  dtutil. Прежде чем писать скрипты, я посмотрел советы специалистов в интернете. Вот здесь неплохая статья, на основании которой я строил свое решение : Using dtutil to copy SSIS packages stored in SQL Server

Что нужно сделать:
1. Скопировать структуру папок на сервере SSIS, в которые установлены наши пакеты
2. Создать скрипт для переноса всех пакетов на основании утилиты dtutil
3. Запустить скрипт

Как далать массовую загрузку пакетов SSIS в хранилище

Не так давно на моей работе мы проводили миграцию с MSSQL2008 на MSSQL2014. Больше всего хлопот доставил перенос пакетов SSIS. Учитывая, что в новой версии SSIS поменялись типы объектов и вообще структура пакета, для преобразования придется открыть все проекты и конвертировать пакеты штатными средствами. Это простой, но довольно утомительный процесс, который я здесь не буду описывать.

Когда все пакеты преобразованы, их нужно установить на новом сервере (здесь я имею ввиду, что хранилище пакетов MSSQL сервера). Если вы используете project deployment model, то, возможно, этот процесс будет тривиальным. Проблемы могут возникнуть при использовании package deployment model. Штатными средствами Visual Studio нет возможности осуществить деплоймент всех пакетов проекта одной операцией - нужно каждый пакет устанавливать отдельно. Если вы используете утилиту BIDSHelper, то она позволяет осуществлять массовую установку пакетов проекта. В свойствах проекта на закладке Deploy нужно лишь указать куда устанавливать пакеты. К сожалению, у утилиты BIDSHelper не очень много настроек для управления процессами деплоймента пакетов, и поэтому, если ваши пакеты хранят sensitive данные (ProtectionLevel = EncryptSensitiveWithPassword) и обычно вы его сохраняете в хранилище с опцией rely on server storage and roles for access control, то утилита сохранит их некорректно.

Что можно сделать в данной ситуации?

Использование рекурсии в CTE

Задачка:
Есть табличка с 3мя полями
ID DATE QTY

Для каждого клиента(ID) каждый день(DATE) может быть запись с количеством(QTY) N.
Нужно выбрать для каждого клиента(ID) все записи по такому принципу:
1. Находим для ID первую запись, где QTY больше 5 (если меньше - пропускаем) - выбираем эту запись
2. Находим для ID следующую запись, где QTY больше 5 и прошло не меньше 14 дней от даты предыдущей выбранной записи - выбираем эту запись
3. Повторяем итерацию 2, пока не выберем все записи

Решение.

Ошибка Database Engine Tuning Advisor

Периодически я использую в своей работе эту замечательную утилитку (Database Engine Tuning Advisor). Иногда она дает больше советов, чем ты можешь обнаружить при изучении плана.
С некоторых пор при анализе разных запросов я стал получать ошибку уже на первом этапе Submiting Configuration Information. Ошибка очень неинформативная:

TITLE: DTAEngine
----------------------------
Error on line 149, position 36 in "file:///C:/DOCUME~1/sergeyg/LOCALS~1/Temp/%23Input.XML".

Порывшись в интернете, нашел по этому поводу только вот это:
http://social.msdn.microsoft.com/Forums/nl-NL/sqldatabaseengine/thread/24ef5abe-9b3a-453e-a912-3aa5268666d1

Ссылка на статью "Семь смертных грехов программиста на T-SQL"

Неплохая статейка о том, как  лучше писать запросы на T-SQL
http://habrahabr.ru/post/149235/

Импорт данных из файлов на удаленной машине с ограниченным доступом

Задача: импортировать данные из файлов, которые лежат на шаровой папке с ограниченным доступом в базу данных MSSQL. Пользователь, который запускает пакет импорта, не имеет доступа к папке с файлами, однако нам известен логин и пароль, которые позволяют получить доступ.

Решение:

Удаление больших объемов данных

Иногда возникают задачи, когда необходимо удалить из таблицы большое количество строк (миллионы, десятки миллионов). При удалении возникают блокировки таблицы, идет запись в транзакшн лог и если подходить к решению данной задачи по простому, то окончания операции можно не дождаться (или через какое-то время получить сообщение об ошибке, в котором будет сказано, что закончилось свободное место на диске и лог писать больше некуда).

Если необходимо удалить бОльшую часть таблицы, то лучшим решением будет такое:
1. Создание новой таблицы с такой же структурой, но без индексов.
2. Копирование из исходной в новую только тех строк, которые должны остаться в исходной.
3. Создание всех необходимых индексов.
4. Удаление исходной таблицы и переименование новой.

Если же все таки по каким то причинам нужно именно удалять записи, то рекомендуется это делать небольшими порциями:

SET ROWCOUNT 5000

WHILE @@ROWCOUNT <> 0
  DELETE TableName
  WHERE  DateForDel >= '20000101'
         AND DateForDel <= '20120101'

SET ROWCOUNT 0 

Автоматический запуск пакетов SSIS по нестандартному расписанию.

Что мы имеем в наличии: хранилище данных с большим количеством таблиц (>100), которое обновляется путем импорта данных из внешнего источника данных (ERP). Для импорта таблиц создано несколько пакетов. Для небольших таблиц существуют только пакеты полной загрузки (т.е. удаление данных в таблице хранилища и загрузка всех данных) для больших таблиц (>50 млн записей) существуют как пакеты полной загрузки так и пакеты инкрементальной загрузки (удаляются данные за определенный период и заново загружаются из источника). Для удобства создано 3 пакета, в которых объединены пакеты по таким признакам:
1 пакет - все пакеты, которые осуществляют только полную загрузку таблиц
2 пакет - все пакеты, которые осуществляют инкрементальную загрузку больших таблиц
3 пакет - все пакеты, которые осуществляют полную загрузку больших таблиц
Каждому пакету полной загрузки из группы 2 соответствует пакет инкрементальной загрузки из группы 3.
Задача: настроить ежедневную автоматическую загрузку хранилища. В определенные дни нужно осуществлять полную загрузку, а в остальные инкрементальную. Должна быть возможность запускать полную загрузку в любой день при необходимости.

Баг в компоненте Sort Transformation в SSIS 2008

Если вы используете в своих пакетах трансформации типа  Merge Join Transformation и Merge Transformation, то иногда приходится пользоваться компонентом Sort Transformation. Компонент этот довольно медленный и рекомендуется избегать его применения. Я в своих проектах очень редко пользуюсь такими трансформациями и стараюсь реализовать логику с помощью запросов (кстати, микрософт рекомендует именно так работать). Чем это лучше использования трансформаций? Во первых, запросы выполняются на сервере, во вторых, при соединениях таблиц используются оптимизационные механизмы сервера. В результате весь процесс работает быстрее.

Использование XML конфигурации в SSIS в картинках

В предыдущей статье Использование XML конфигурации в SSIS была описана методика использования XML конфигураций. Но то, что описано словами гораздо хуже воспринимается чем то, что описано с использованием изображений. Ниже данная методика в картинках.

1. Есть два сервера: тестовый и основной. Есть компьютер разработчика.
Создаем на каждой из этих машин папку SSIS_Config на диске C:\ (Из соображений, что такой диск будет на всех
машинах).

2. У нас есть 3 базы данных, которые используются пакетами SSIS

Картинка 1

Использование XML конфигурации в SSIS

Самая типичная организация работы системы BI - это наличие минимум двух серверов баз данных. Один - тестовый, второй  - основной. В идеальном случае эти сервера должны быть одинаковы. Тогда при тестировании решений на тестовом сервере можно видеть как система будет работать на основном. При разработке решений SSIS в среде с несколькими серверами необходимо использовать механизм конфигураций. Чаще всего используются 2 типа конфигураций: SQL server и XML.

Проблема с импортом плоского файла (flat file) в SSIS 2008

Задача: перенести несколько пакетов DTS (MSSQL 2000) на SSIS MSSQL 2008.
Учитывая то, что нормального мастера для переноса DTS пакетов в SSIS не существует, приходится такую работу делать вручную, т.е. строить новые пакеты с такой же функциональностью.

В пакете, который мне нужно было перенести, информация из текстового файла импортируется в таблицу базы данных. Импорт в DTS работает точно так же, как и импорт Excel - открываешь файл, указываешь разделители столбцов и строк, кодировку и тд и в результате получаешь нужные данные. По тому же пути пошел, когда начал создавать SSIS пакет: первым делом необходимо настроить Flat file connection. При настройке обнаружилось, что конекшн видит меньше столбцов чем есть на самом деле. Сначала я подумал, что механизм обрабатывает первую строку и на ее основании формирует описание столбцов. Но оказалось все по другому. Оказалось, что если в строке файла недостаточное количество разделителей (т.е. разное количество во всех строках), то импорт будет работать некорректно.