---------------------
-- Begin Section A --
---------------------
set nocount on
--Declaring variable
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

-------------------
-- End Section A --
-------------------

---------------------
-- Begin Section 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) 

--Creating object
 EXEC @rc = sp_OACreate 'DTS.Package', @object OUTPUT
  IF @rc <> 0 goto PrintError

-- Load Package from Source Server
 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'
-------------------
-- End Section B --
-------------------
---------------------
-- Begin Section C --
---------------------
-- Get Number of Connections 
  EXEC @rc = sp_OAGetProperty @object, 'Connections.Count', @Numof OUT
  IF @rc <> 0 goto PrintError

  set @i = 0
-- Process Through each Connection
  While @i < @Numof 
  begin 
    set @i = @i + 1

-- Get Name of Connections 
    set @property = 'Connections(' + rtrim(cast(@i as char)) + ').Name'
    EXEC @rc = sp_OAGetProperty @object, @property, @property_value OUT
    IF @rc <> 0 goto PrintError

-- Change SERVER1 to SERVER2 in  Connection Name
    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

-- Get DataSource of Connections 
    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
-------------------
-- End Section C --
-------------------

---------------------
-- Begin Section D --
---------------------
-- Get Number of Task 
  EXEC @rc = sp_OAGetProperty @object, 'Tasks.Count', @Numof OUT
  IF @rc <> 0 goto PrintError
  set @i = 0

-- Process Through each Task
  While @i < @Numof 
  begin 
    set @i = @i + 1
 
-- Get Number of Properties
    set @property = 'Tasks(' + rtrim(cast(@i as char)) + 
                         ').Properties.Count'
    EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT
    IF @rc <> 0 goto PrintError
   
 -- Process through all properties
    set  @j = 0 
    while @j < @NumofItems
    begin 
      set @j = @j + 1

 -- Get Name of Property
      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
 
 -- Get Value of Property
      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
 
 -- Change SERVER1 to SERVER2 in Value of Each Property
      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
-------------------
-- End Section D --
-------------------

---------------------
-- Begin Section E --
---------------------
 -- Get Number of Steps 
  EXEC @rc = sp_OAGetProperty @object, 'Steps.Count', @Numof OUT
  IF @rc <> 0 goto PrintError
 
  set @i = 0
 -- Process Through each Step
  While @i < @Numof 
  begin 
    set @i = @i + 1
 
 -- Get Number of Properties
 
    set @property = 'Steps(' + rtrim(cast(@i as char)) + 
                    ').Properties.Count'
    EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT
    IF @rc <> 0 goto PrintError
   
 -- Process through all properties
    set  @j = 0 
    while @j < @NumofItems
    begin 
      set @j = @j + 1
 
  -- Get Value of Each Property
      set @property = 'Steps(' + 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
 
 -- Change SQLPROD1 to DOHDBOLYPR01 in Value of Each Property
 
      if charindex('SERVER1',@property_value) > 0
      begin 
        Print 'Changed Steps.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
-------------------
-- End Section E --
-------------------

---------------------
-- Begin Section F --
--------------------- 
-- Remove package from Server2
  EXEC @rc = sp_OAMethod @object, 'RemoveFromSQLServer', 
                        NULL,SERVER2,'','','256','','',@pkgname

  IF @rc <> 0 and @rc <> -2147217900 -- the -2147217900 is the return code if package does not exist
     goto PrintError
 
-- Save DTS package to Server2
  EXEC @rc = sp_OAMethod @object, 'SaveToSQLServer', 
                          NULL,'SERVER2', '','','256'
  IF @rc <> 0 goto PrintError
  Print 'Package Saved Successfully'
end
return

-- Process Errors
PrintError:
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc
-------------------
-- End Section F --
-------------------