Достаточно просто вручную скопировать один 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 пакета.