Выгрузка SSIS-пакетов с сервера MS SQL

Время прочтения: 7 мин.

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

В нашем случае необходимо отказаться от использования трех MS SQL серверов, при этом сохранить все наработки, размещенные на серверах для переиспользования в случае необходимости.

В этой публикации расскажем, как можно быстро экспортировать все SSIS пакеты с нескольких MS SQL серверов, чтобы в дальнейшем можно было локально извлекать и использовать необходимые скрипты.

Поискав на просторах интернета, мы поняли, что информации про выгрузку SSIS пакетов практически нет. Погрузившись и изучив вопрос, нам удалось найти решение, которым делимся с вами.

Общий алгоритм следующий:

Сначала собираем все пакеты с нужных нам серверов в отдельную табличку в виде двоичных данных.
Затем создаем исполняемый файл сценария PowerShell Import_SSIS.ps1, создающий структуру папок SSISDB на диске и выгружающий пакеты в файлы.
После чего, исполняем сценарий из файла PowerShell Import_SSIS.ps1, запустив файл Run_PScript.cmd.

Все это реализовано через SSIS-пакет.

Создадим таблицу в БД MS SQL для сбора SSIS пакетов в двоичном виде (например, на сервере serv 1):

CREATE TABLE [TOOLS].[SSIS_Projects](
     [Folder_Name] [sysname] NOT NULL,
     [Project_Name] [sysname] NOT NULL,
     [Ispac_data] [varbinary](max) NULL,
     [Server_Name] [sysname] NULL,
     [Full_Server_Name] [sysname] NULL
) ON [ADM_TOOL_SERVICE] TEXTIMAGE_ON [ADM_TOOL_SERVICE]

Далее в Visual Studio (SSDT) создаем показанную ниже структуру SSIS пакета:

Описание элементов структуры (Рисунок 1):

1. «Trunc SSIS_Projects» — Очистка таблицы[DB_TOOL].[TOOLS].[SSIS_Projects]

2. «Fill serv1», «Fill serv2», «Fill serv3» — заполнение таблицы бинарными данными SSIS пакетов со всех трех серверов

3. «Make Server Dir cmd» — создание папок для каждого сервера. Внутри элемента создаем структуру:

3.1. «Источник “OLE DB”» — запрос, формирующий строки сценария создания структуры папок

3.2. «Назначение “Неструктурированный файл”» — создание самого файла с данными

4. «Make Folder Dir cmd» — создания папок структуры SSISDB. Внутри элемента создаем структуру:

4.1. «Источник “OLE DB”» — запрос, формирующий строки сценария создания структуры папок

4.2. «Назначение “Неструктурированный файл”» — добавление данных в файл

5. «Make Import SSIS cmd» — выгрузки пакетов в файлы. Внутри элемента создаем структуру:

5.1. «Источник “OLE DB”» — запрос, формирующий строки сценария для выгрузки пакетов в бинарные файлы

5.2. «Назначение “Неструктурированный файл”» — добавление данных в файл

6. «Задача “Выполнение процесса”» — запуск файла на исполнение

Далее выполним каждый шаг подробнее:

В элемент «Trunc SSIS_Projects» (Execute SQL Task) добавляем скрипт очистки созданной таблицы «SSIS_Projects»:

truncate table [DB_TOOL].[TOOLS].[SSIS_Projects]

2. Заполняем таблицу бинарниками пакетов со всех трех серверов. Для этого, в каждом из элементов: «Fill serv1», «Fill serv2», «Fill serv3» настраиваем соединение с сервером и вставляем следующий скрипт в свойство «SQLstatement»:

declare @F_Name sysname, @P_Name sysname 
declare @SSIS_Projects_Bin TABLE ([Ispac] [varbinary](max))

DECLARE PRJ_LIST CURSOR FOR
select 
   f.[name] as Folder_Name
   ,pro.[name] as Project_Name
from
[SSISDB].[catalog].[folders] f
inner join [SSISDB].[catalog].[projects] pro on pro.folder_id = f.folder_id

OPEN PRJ_LIST;
FETCH NEXT FROM PRJ_LIST INTO @F_Name, @P_Name
WHILE @@FETCH_STATUS = 0
BEGIN

delete from @SSIS_Projects_Bin

insert into @SSIS_Projects_Bin
EXECUTE [SSISDB].[catalog].[get_project] @folder_name =  @F_Name,@project_name = @P_name

insert into [SERV1].[DB_TOOL].[TOOLS].[SSIS_Projects]
(
[Folder_Name]
,[Project_Name]
,[Ispac_data]
,[Server_Name]
,[Full_Server_Name]
)
values
(
@F_Name
,@P_name
,(select Ispac from @SSIS_Projects_Bin)
,CASE WHEN charindex('\',@@SERVERNAME) > 0 THEN substring(@@SERVERNAME,1,charindex('\',@@SERVERNAME) - 1)
  ELSE @@SERVERNAME
  END
,@@SERVERNAME
)

FETCH NEXT FROM PRJ_LIST INTO @F_Name, @P_Name
END;
CLOSE PRJ_LIST;
DEALLOCATE PRJ_LIST;

3. В элементах «Make Server Dir cmd», «Make Folder Dir cmd», «Make Import SSIS cmd» создаем два элемента, как показано на Рисунке 4:

Для всех трех элементов: «Make Server Dir cmd», «Make Folder Dir cmd», «Make Import SSIS cmd» настраиваем свойства внутреннего элемент «Назначение “Неструктурированный файл”» (как показано на Рисунках 5 и 6):

Обратите внимание, что галку «Overwrite data in the file» оставляем, чтобы создать файл.

Для элемента: «Make Server Dir cmd» во внутреннем элементе «Источник “OLE DB”» в свойство SQL command вставляем скрипт, представленный ниже. Где «\\SQLSF\fir\ETL\SSIS\’ + Server_Name + ‘» — это путь к расшаренной папке, в которую будут выгружаться SSIS пакеты (Рисунок 7):

select distinct cast('if (![System.IO.Directory]::Exists("\\SQLSF\fir\ETL\SSIS\' + Server_Name + '")) {  md "\\SQLSF\fir\ETL\SSIS\' + Server_Name + '" }' as nvarchar(2000)) as Create_Folder_Server
 FROM [DB_TOOL].[TOOLS].[SSIS_Projects]

4. Для элемента: «Make Folder Dir cmd» во внутреннем элементе «Назначение “Неструктурированный файл”» убираем галку «Overwrite data in the file», чтобы в файл дописывать данные (Рисунок 8).

Для элемента: «Make Folder Dir cmd» во внутреннем элементе «Источник “OLE DB”» в свойство SQL command вставляем скрипт:

select distinct cast('if (![System.IO.Directory]::Exists("\\SQLSF\fir\ETL\SSIS\' + Server_Name + '\' + Folder_Name + '")) {  md "\\SQLSF\fir\ETL\SSIS\' + Server_Name + '\' + Folder_Name + '" }' as nvarchar(2000)) as Create_Folder_Folder
FROM [DB_TOOL].[TOOLS].[SSIS_Projects]

5. Для элемента: «Make Import SSIS cmd» во внутреннем элементе «Назначение “Неструктурированный файл”» убираем галку «Overwrite data in the file», чтобы в файл дописывать данные.

Для элемента: «Make Import SSIS cmd» во внутреннем элементе «Источник “OLE DB”» в свойство SQL command вставляем скрипт:

select  
cast('bcp "select [Ispac_data] from [DB_TOOL].[TOOLS].[SSIS_Projects] where [Folder_Name] = ''' + [Folder_Name] + ''' and  [Project_Name] = ''' + [Project_Name] + '''" queryout "\\SQLSF\fir\ETL\SSIS\' + Server_Name + '\' + Folder_Name + '\' + [Project_Name] + '.ispac" -S serv1 -f \\SQLSF\fir\ETL\SSIS\bcp.fmt -T' as nvarchar(2000)) as Text_Query
 FROM [DB_TOOL].[TOOLS].[SSIS_Projects]

3, 4, 5 шаги формируют файл Import_SSIS.ps1 с командами PowerShell, которые формируют файловую структуру и выгружают в нее SSIS пакеты при помощи утилиты «bcp».

Для корректной выгрузки нужно также настроить файл форматирования (bcp.fmt), указав нулевую длину префикса поя с данными. Про утилиту bcp и формат файла форматирования можно прочитать на сайте Microsoft.

Создаем файл Run_PScript.cmd – вручную со скриптом:

powershell -ExecutionPolicy Bypass "\\SQLSF\fir\ETL\SSIS\Import_SSIS.ps1"

7. В элементе «Задача “Выполнение процесса”» в свойство Executable во вкладке Process пропишем строку запуска:

\\SQLSF\fir\ETL\SSIS\Run_PScript.cmd

Публикуем SSIS пакет на сервере и запускаем его на исполнение.

В итоге, получается готовая структура вида:

И в папке serv* выгруженные SSIS пакеты соответствующего сервера:

В каждой папке SSIS пакета будут файлы формата .ispac. Если переименовать данные файлы с расширением .rar, то обычной программой Winrar можно увидеть внутренности архива:

Результат

Таким образом можно выгрузить разом все SSIS пакеты за относительно короткое время и сохранить для переиспользования в репозиторий.

Выгрузка SSIS-пакетов с сервера MS SQL

01.06.2023 Исаев Александр, Фокина Ольга, г. Москва