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

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

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



Делаем пошагово.

1. Скопировать структуру папок на сервере SSIS, в которые установлены наши пакеты
В приведенной статье запросы написаны для версий MSSQL2005-2008. Если у вас более поздние, но нужно немного переписать запросы, использовав в них новые имена таблиц. В моем случае используется сервер MSSQL2014. Структура наших папок двухуровневая, поэтому я не стал писать сложные рекурсивные запросы, чтобы получить сразу все папки. Делается 2 запроса - один для первого уровня, а второй для второго:

--CREATE ROOT FOLDERs
SELECT  '"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /SOURCESERVER Dev /FC SQL;\;"' + f.foldername +'"', 1 AS SortOrder
FROM msdb.dbo.sysssispackagefolders f
WHERE f.parentfolderid = '00000000-0000-0000-0000-000000000000'

UNION

--CREATE SUBFOLDERs
SELECT '"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /SOURCESERVER Dev /FC SQL;"' + pf.foldername + '";"' + f.foldername +'"' , 2 AS SortOrder
FROM msdb.dbo.sysssispackagefolders f
JOIN (
SELECT folderid,
foldername
FROM msdb.dbo.sysssispackagefolders 
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
) pf
ON f.parentfolderid = pf.folderid

ORDER BY SortOrder

В результает запуска запроса, мы получаем набор строк, примерно, следующего содержания:

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /SOURCESERVER Dev /FC SQL;\;"Main" - для первого уровня

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /SOURCESERVER Dev /FC SQL;"Main";"BL" - для второго уровня

Полученные строки мы вставляем в bat файл и запускаем его из командной строки. Каждая команда создаст (параметр /FC) новую папку на сервере Dev (/SOURCESERVER Dev). Первым именем идет родительская папка, а вторым дочерняя (в случае первого уровня, в качестве родительской указываем \)

2. Создать скрипт для переноса всех пакетов на основании утилиты dtutil
Скрипт создаем на основании запросов по копированию папок и примере командной строки, которую я описывал в предыдущей статье. Разница командных строк лишь в том, что в прошлый раз мы сохраняли в хранилище пакеты, которых хранились у нас на диске ввиде файлов, а теперь мы копируем их с одного SQL сервера на другой (с Prd на Dev).

-- packages Level1
SELECT  '"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /Decrypt "1" /SQL "' + f.foldername + '/' + p.name + '" /Encrypt SQL;"' + f.foldername + '/' + p.name + '";5 /SOURCESERVER Prd /DESTSERVER Dev /Quiet '
FROM msdb.dbo.sysssispackagefolders f
JOIN msdb.dbo.sysssispackages p
ON p.folderid = f.folderid
WHERE f.parentfolderid = '00000000-0000-0000-0000-000000000000'
AND foldername <> 'Data Collector'

UNION

-- packages Level2
SELECT '"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /Decrypt "1" /SQL "' + pf.foldername + '/' + f.foldername + '/' + p.name + '" /Encrypt SQL;"' + pf.foldername + '/' + f.foldername + '/' + p.name + '";5 /SOURCESERVER Prd /DESTSERVER Dev /Quiet '
FROM msdb.dbo.sysssispackagefolders f
JOIN msdb.dbo.sysssispackages p
ON p.folderid = f.folderid

JOIN (
SELECT folderid,
foldername
FROM msdb.dbo.sysssispackagefolders
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
) pf
ON f.parentfolderid = pf.folderid


Получаем строки скрипта:
"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /Decrypt "1" /SQL "Main/BL/BL_Main" /Encrypt SQL;"Main/BL/BL_Main";5 /SOURCESERVER Prd /DESTSERVER Dev /Quiet

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /Decrypt "1" /SQL "Main/Prd_Main" /Encrypt SQL;"Main/Prd_Main";5 /SOURCESERVER Prd /DESTSERVER Dev /Quiet

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtutil.exe" /Decrypt "1" /SQL "Main/Cubes_Process" /Encrypt SQL;"Main/Cubes_Process";5 /SOURCESERVER Prd /DESTSERVER Dev /Quiet

...

3. Запускаем скрипт и проверяем результаты его работы.