Перемещение DTS пакетов

Перевод Маргариты Баскаковой

Достаточно просто вручную скопировать один DTS пакет с одной машины SQL Server на другую. Но что, если Вы хотите скопировать все имеющиеся DTS пакеты с SERVERA на SERVERB, потому что Вы выводите из эксплуатации SERVERA? Если у вас сотни DTS пакетов то, копирование их вручную отнимет у вас очень много времени. Эта статья расскажет о том, как можно использовать SQL-DMO и модель объекта DTS, чтобы быстро скопировать большое количество DTS пакетов.
Вдобавок к обычному сохранению пакета на новом сервере существует множество вещей, которые следует учесть, копируя DTS пакет. Как правило, когда Вы копируете пакет вам также необходимо изменить строку подключения. Это нужно для того, чтобы пакет, который был скопирован, обращался вместо старого сервера к новому. Кроме того, пакеты могут иметь входные или выходные файлы, которые, возможно, должны измениться, когда Вы копируете пакеты. Такие изменения файла могут понадобиться, так как Вы используете имена UNC; следовательно, они должны измениться, когда пакеты копируются на новый сервер, или файлы не находятся на том же самом диске или каталоге на обоих серверах. Может быть множество других изменений, которые необходимо сделать, когда Вы копируете пакет. Так при использовании объектной модели DTS, можно исследовать и управлять DTS Пакетами, автоматически основанными на требованиях, которые Вы определяете.
Прежде, чем изучить метод копирования и изменения DTS пакетов при помощи SQL-DMO, рассмотрим два различных DTS пакета, которые необходимо скопировать с SERVER1 на SERVER2. Первый пакет с именем "MyPackage1" создает простой файл из таблицы базы данных. На Рис. 1 приведена иллюстрация этого пакета в режиме DTS Designer:

Рис. 1

Эта иллюстрация показывает свойства подключения "Microsoft OLE DB provider for SQL Server" с именем "SERVER1". Следующая иллюстрация показывает свойства подключения "Text File (Destination)" (см. Рис.2). Заметьте, что имя сервера "SERVER1" появится среди свойств этих двух подключений. Далее будет приведён сценарий T-SQL, который изменит все имеющиеся строки, ссылающиеся на имя "SERVER1" на имя нового сервера назначения - "SERVER2."

Рис. 2

Следующая иллюстрация демонстрирует второй пакет, запланированный для миграции, с именем "MyPackage2": Этот пакет так же содержит подключение "Microsoft OLE DB provider for SQL Server" с именем "SERVER1" как показано на предыдущем рисунке, и имеет "Execute SQL Task " чтобы усечь таблицу. Еще раз, свойства этого пакета также имеют строку, "SERVER1" среди свойств пакета; необходимо изменить эту строку, при миграции этого пакета на "SERVER2".

Рис. 3

Чтобы продемонстрировать, как использовать SQL-DMO, чтобы скопировать и изменить эти два пакета с одного сервера на другой, воспользуемся сценарием T-SQL. Этот сценарий T-SQL использует OLE Automation, чтобы анализировать различные объекты, коллекции, и свойства объектной модели DTS для выполнения миграции. Здесь приведён полный текст сценария.
Первая часть сценария (см. раздел A) определит локальные переменные, которые потребуются в ходе его работы, создаст и заполнит временную таблицу списком всех пакетов на SERVER1. Ниже приведён код раздела A:

set nocount on --определение переменных DECLARE @object int DECLARE @pkgname nvarchar(255) declare @rc int DECLARE @src varchar(255) Declare @desc varchar(255) Declare @Numof int Declare @NumofItems int declare @i int declare @j int Declare @property varchar(8000) Declare @property_value varchar(8000) Declare @property_name varchar(8000) -- Get list of Packages from KB article 241249 if exists(select * from tempdb.dbo.sysobjects where name like '#dts_package____%') drop table #dts_packages create table #dts_packages (name varchar(1000), id uniqueidentifier, versionid uniqueidentifier, description varchar(1000), createdate datetime, owner varchar(100), size int, packagedata image, isowner varchar(100), packagetype int ) insert into #dts_packages exec msdb..sp_enum_dtspackages

Чтобы получить имена всех пакетов, используется хранимая процедура sp_enum_dtspackages. Это недокументированная хранимая процедура, расположенная в базе данных msdb. Подробная информация об этой процедуре может быть найдена здесь. Как можно было заметить в ранее приведенном коде, имена пакетов получены от сервера, на котором выполняется этот сценарий (в этом случае, SERVER1). Как только сценарий нашёл имена всех DTS пакетов сервера "SERVER1", каждый DTS пакет обрабатывается и каждая ссылка на "SERVER1" заменяется ссылкой на "SERVER2".
Следующий раздел, раздел B, запускает цикл WHILE для того, чтобы обработать каждый DTS пакет, и загружает каждый пакет в память с целью проверки на упоминание "SERVER1" в остальной части сценария. Ниже приведён код раздела B:

while (select count(*) from #dts_packages) > 0 begin select top 1 @pkgname=name from #dts_packages order by name delete from #dts_packages where name = @pkgname Print 'Starting the migration of package ' + rtrim(@pkgname) --создание объекта EXEC @rc = sp_OACreate 'DTS.Package', @object OUTPUT IF @rc <> 0 goto PrintError -- Загрузка Пакета с Сервера-Источника EXEC @rc = sp_OAMethod @object, 'LoadFromSQLServer', -- Тип аутентификации SQL Server Authentication -- NULL,'SERVER1','login','password','0','','','',@pkgname -- Тип аутентификации Windows Authentication NULL,'SERVER1','','','256','','','',@pkgname IF @rc <> 0 goto PrintError print 'Package loaded successfully'

С очередным проходом через цикл WHILE каждый пакет просматривается, изменяется и копируется на SERVER2. Чтобы загрузить каждый пакет в память используется OLE Automation. Сначала создаётся объект "DTS Package" при помощи хранимой процедуры "sp_OACreate". Как только этот объект успешно создан, код T-SQL загружает текущий пакет, который нужно обработать, в память, используя метод "LoadFromSQLServer". Этот метод требует определить сервер, с которого будет загружаться пакет; метод аутентификации, который будет использоваться (Windows или SQL Server); и пакет, который необходимо загрузить. Приведенный код использует Windows Authentication; однако, в качестве примера так же приведены параметры, используемые при аутентификации SQL Server. При использовании SQL Server Authentication необходимо указать логин (login) и пароль (password). Как только пакет загрузится, будет напечатано сообщение, указывающее, что пакет был успешно загружен.
Примечание: с каждым исполнением OLE Automation переменной @rc присваивается значение, и затем проверяется, чтобы удостовериться, что каждое выполнение завершилось успешно. Если выполнение потерпело неудачу, оператор "GOTO" вызовет переход к сценарию, код которого приведён ниже:

PrintError: EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc

Этот код приводит к исполнению хранимой процедуры "sp_OAGetErrorInfo". Эта процедура печатает пользовательские сообщения об ошибках OLE Automation.

Примечание: этот код приведён в Разделе F.

Следующие разделы обрабатывают свойства пакета, изменяя каждую ссылку "SERVER1" на новый сервер назначения "SERVER2". Ниже приведён код раздела C. Этот раздел обрабатывает информацию о Подключении каждого пакета:

-- Получение числа (количества) подключений EXEC @rc = sp_OAGetProperty @object, 'Connections.Count', @Numof OUT IF @rc <> 0 goto PrintError set @i = 0 -- Обработка каждого подключения While @i < @Numof begin set @i = @i + 1 -- Получение имения подключения set @property = 'Connections(' + rtrim(cast(@i as char)) + ').Name' EXEC @rc = sp_OAGetProperty @object, @property, @property_value OUT IF @rc <> 0 goto PrintError -- Изменение SERVER1 на SERVER2 в имени подключения if charindex('SERVER1',@property_value) > 0 begin Print 'Change Connection.Name for ' + @property set @property_value = replace(@property_value,'SERVER1','SERVER2') EXEC @rc = sp_OASetProperty @object, @property, @property_value IF @rc <> 0 goto PrintError end -- Получение Источника данных подключения set @property = 'Connections(' + rtrim(cast(@i as char)) + ').DataSource' EXEC @rc = sp_OAGetProperty @object, @property, @property_value OUT IF @rc <> 0 goto PrintError -- Change SERVER1 to SERVER2 in Connection Value if charindex('SERVER1',@property_value) > 0 begin Print 'Changed Connection.DataSource for ' + @property set @property_value = replace(@property_value,'SERVER1','SERVER2') EXEC @rc = sp_OASetProperty @object, @property, @property_value IF @rc <> 0 goto PrintError end end

Число подключений, имя каждого подключения и свойства DataSource изменяются, когда коллекция "Connections" перепроверена для каждого подключения. Вы можете найти значения для каждого из этих свойств при помощи хранимой процедуры "sp_OAGetProperty". Ранее приведенный код сначала идентифицирует число подключений, содержавшихся в пакете. Затем, для каждого подключения, сценарий проверяет Имя и DataSource, определяет, указана ли строка "SERVER1". Если строка "SERVER1" найдена, то она заменяется строкой "SERVER2". Для изменения свойств DTS пакетов можно воспользоваться хранимой процедурой "sp_OASetProperty".
Следующий раздел сценария (раздел D) обрабатывает все задачи в каждом пакете. Чтобы обработать каждую задачу воспользуемся немного другим подходом идентификации свойств, рассматриваемых для изменения. Вместо того чтобы называть определенные свойства, которые нужно изменить, обрабатывается каждое свойство в каждой задаче итерационно, затем определяется, должно ли оно измениться или нет. Ниже приведён код раздела D:

-- Получение числа Задач EXEC @rc = sp_OAGetProperty @object, 'Tasks.Count', @Numof OUT IF @rc <> 0 goto PrintError set @i = 0 -- Обработка каждой Задачи While @i < @Numof begin set @i = @i + 1 -- Получение числа свойств set @property = 'Tasks(' + rtrim(cast(@i as char)) + ').Properties.Count' EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT IF @rc <> 0 goto PrintError -- Обработка всех свойств set @j = 0 while @j < @NumofItems begin set @j = @j + 1 -- Получение имени свойства set @property = 'Tasks(' + rtrim(cast(@i as char)) + ').Properties(' + rtrim(cast(@j as char)) + ').Name' EXEC @rc = sp_OAGetProperty @object, @property, @Property_name OUT IF @rc <> 0 goto PrintError -- Получение значения свойства set @property = 'Tasks(' + rtrim(cast(@i as char)) + ').Properties(' + rtrim(cast(@j as char)) + ').Value' EXEC @rc = sp_OAGetProperty @object, @property, @Property_value OUT IF @rc <> 0 goto PrintError -- Замена SERVER1 на SERVER2 в значениях каждого свойства if charindex('SERVER1',@property_value) > 0 begin Print 'Changed Task.Properties for ' + replace(@property,'Value',@property_name) set @property_value = replace(@property_value,'SERVER1','SERVER2') EXEC @rc = sp_OASetProperty @object, @property, @property_value IF @rc <> 0 goto PrintError end end end

Как можно увидеть, сначала определяется число задач в DTS пакете, используя свойство "Task.Count". Затем, обрабатывается каждая задача; вместо того, чтобы конкретно определять свойства, которые нужно проверить и изменить, обрабатывается и проверяется каждое свойство в задаче. Чтобы сделать это, определяется число свойств для каждой задачи при помощи "Properties .Count." Затем выполняется хранимая процедура "sp_OAGetProperty", в которой установленные свойства параметров используют следующий синтаксис, чтобы вернуть Имя и свойства Value соответственно:

Tasks(@i).Properties(@j).Name Tasks(@i).Properties(@j).Value

В этом синтаксисе, "@i" идентифицирует определенную обрабатываемую задачу, а "@j" идентифицирует определенное свойство в пределах задачи. Если какое-нибудь значение свойства содержит строку "SERVER1", то с помощью хранимой процедуры "sp_OASetProperty" будет произведена заменена на строку "SERVER2".
Следующий раздел (Раздел E) обрабатывает все шаги. Поскольку этот раздел обрабатывает информацию о шаге точно так, как, это делают задачи, Раздел E в данной статье рассматриваться не будет. Код Раздела Е можно увидеть в общем сценарии.
Наконец, сохраним измененный пакет на новом сервере. Этот процесс выполняется в два шага.
Ниже приведён код раздела F:

-- Удаление пакета с Server2 EXEC @rc = sp_OAMethod @object, 'RemoveFromSQLServer', NULL,SERVER2,'','','256','','',@pkgname IF @rc <> 0 and @rc <> -2147217900 - это возвращаемый код в случае, если пакет на существует goto PrintError -- Сохранение DTS пакета на Server2 EXEC @rc = sp_OAMethod @object, 'SaveToSQLServer', NULL,'SERVER2', '','','256' IF @rc <> 0 goto PrintError Print 'Package Saved Successfully' end return -- Обработка ошибок PrintError: EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc

Первый шаг этого процесса использует метод "RemoveFromSQLServer" чтобы удалить заменяемый пакет с сервера назначения (Server2). В случае, если пакет уже существует на сервере назначения, использование этого метода гарантирует, что будет скопирован пакет, а не добавлена новая версия. (Примечание: проверяется два различных сообщения об ошибках, возвращаемых из выполнения метода "RemoveFromSQLServer". Возвращаемый код "-2147217900" указывает, что пакет, который нужно скопировать, не существует на сервере назначения.) Затем, с помощью метода "SaveToSQLServer",пакет сохраняется на сервере назначения. Последний показанный кусочек кода - это код, показывающий ошибки, возвращаемые при исполнении различных хранимых процедур OLE Automation.
При запуске полного сценария получаем на выходе данные, которые показывают, какие свойства DTS пакета были изменены:

Starting the migration of package MyPackage1 Package loaded successfully Change Connection.Name for Connections(1).Name Change Connection.Name for Connections(2).Name Changed Connection.DataSource for Connections(2).DataSource Changed Task.Properties for Tasks(1).Properties(7).DestinationObjectName Package Saved Successfully Starting the migration of package MyPackage2 Package loaded successfully Change Connection.Name for Connections(1).Name Changed Connection.DataSource for Connections(1).DataSource Changed Task.Properties for Tasks(1).Properties(2).Description Changed Task.Properties for Tasks(1).Properties(3).SQLStatement Changed Steps.Properties for Steps(1).Properties(2).OutputAsRecordset Package Saved Successfully

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