Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » SQL Server » SQL Server Articles Archive » Moving DTS Packages
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554
 

Moving DTS Packages

by Gregory A. Larsen

It is fairly easy to manually copy a single DTS package from one SQL Server machine to another. But what if you want to copy all the DTS packages on SERVERA to SERVERB because you are decommissioning SERVERA? If you have hundreds of DTS packages, copying the packages manually would become a very time-consuming task. This article will discuss how you can use SQL-DMO and the DTS object model to quickly copy many DTS packages.

There are a number of things to consider when copying a DTS package, in addition to simply saving the package on a new server. Typically, when you copy a package, you will also need to change connection strings. You normally do this because you want the package being copied to access the new server instead of the old server. Also, your packages may have input or output files that may need to change when you copy the packages. Such file changes might be necessary because you are using UNC names; therefore, they need to change when the packages are copied to a new server, or the files are not in the same drive or directory on both servers. There could be a number of other changes that need to be made when you copy a package. So by using the DTS object model, we can examine and manipulate DTS Packages automatically based on requirements you define.

Before I go through how to copy and modify DTS Packages using SQL-DMO, let’s review two different DTS packages that I will be copying from SERVER1 to SERVER2. The first package, named “MyPackage1,” creates a flat file from a database table. The following is what it looks like in DTS Designer:

This screen shot shows the simple package, plus the properties of the “Microsoft OLE DB provider of SQL Server” connection named “SERVER1.” The following is another screen shot that shows the properties of the “Text File (Destination)” connection. Note that the server name, “SERVER1,” appears in amongst the properties of these two connections. I will be walking you through a T-SQL script that will change all occurrences of the character string, “SERVER1” to the new target server, “SERVER2.”

The next screen shot shows is the second package I plan to migrate, called “MyPackage2”: This package contains the same “Microsoft OLE DB provider of SQL Server” connection named, “SERVER1” as shown in the previous image, and has an “Execute SQL Task” to truncate a table. Once again, the properties of this package also have the string, “SERVER1” in amongst the package properties; I will need to change this string when I migrate this package to “SERVER2.”

To demonstrate how to use SQL-DMO to copy and modify these two packages from one server to another, I will walk you through a T-SQL script I developed, one piece at a time. This T-SQL script uses OLE Automation to explore the different objects, collections, and properties of the DTS object model to accomplish this migration. Click here to view the complete script.

The first part of the script I will be reviewing (section A) will establish the local variables needed by this script, and it will create and populate a temporary table with a list of all packages on SERVER1. Here is the code for 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

To obtain the names of all the packages, I use the stored procedure sp_enum_dtspackages. This stored procedure is an undocumented stored procedure located in the msdb database. More information about the procedure can be found in a knowledgebase article on the Web at http://support.microsoft.com/default.aspx?scid=kb;en-us;241249. As you can see from the previously shown code, I obtain the name of the packages from the server where I run this script (in this case, SERVER1). Once the script has retrieved the names for all the DTS packages on SERVER1, it processes through each DTS package, one package at a time, changing all references of “SERVER1” to “SERVER2.”

The next section, section B, starts a WHILE loop for processing through each DTS package, and loads each package into memory so it can be inspected for occurrences of “SERVER1” by the rest of the script. Here is the code for 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'

With each pass through the WHILE loop, a single package is reviewed, modified, and copied to SERVER2. To load each package into memory, I am using OLE Automation. First, I create a “DTS.Package” object by using the stored procedure, “sp_OACreate.” Once this object is successfully created, the T-SQL code loads the current package to be processed into memory using the “LoadFromSQLServer” method. This method requires that you identify the server from which you will load the package, which authentication method you are going to use (Windows or SQL Server), and the package you are going to load. My code uses Windows Authentication; however, I also showed the parameters needed to use SQL Server authentication, which is commented out in my script. When using SQL Server Authentication, you must provide a login and password. Once the package has been loaded, a message indicating the package has been successfully loaded is printed. Note that with each execution of OLE Automation, the @rc variable is set, and then tested to make sure each execution is successful. If the execution is not successful, a “GOTO” statement is generated that causes control to jump to the bottom of this script where the following code is executed:

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

This code executes the “sp_OAGetErrorInfo” stored procedure. This procedure prints a user-friendly OLE Automation error messages. Note this code is contained in Section F.

The next few sections process through the package properties changing each reference to “SERVER1” to the new target server “SERVER2.” The following code is from section C. This section processes through the Connection information of each package:

 
 -- 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

The number of connections, name of each connection, and DataSource properties are changed when the “Connections” collection is reviewed for each connection. You can retrieve the values for each of these properties by using the “sp_OAGetProperty” stored procedure. The previously shown code first identifies the number of connections contained in the package. Then, for each connection, the script checks the Name and DataSource, to determine if the string “SERVER1” is specified. If string “SERVER1” is found, it is replaced with the string, “SERVER2.” To change the DTS package properties, you can use the “sp_OASetProperty” stored procedure.

The next section of the script (section D) processes through all the tasks in each package. To process through each task, I take a little different approach to identify which properties to look at and change. Instead of naming the specific properties that I want to change, I iteratively process through each property in each task, then determine whether it needs to change or not. Here is the code for 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

As you can see, I first determine the number of tasks in the DTS package, using the “Tasks.Count” property. Then, I process through each task; rather than specifically identifying the properties I want to review and change, I process through each property in a task and look at each one. To do this, I determined the number of properties for each task by using the “Properties.Count.” I then execute the “sp_OAGetProperty” stored procedure, in which the property parameter settings take on the following syntax to return the Name and Value properties respectively:

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

In this syntax, “@i” identifies the specific task being processed, and “@j” identifies the specific property within the task. If any property value contains the string “SERVER1,” it is changed to “SERVER2” using the “sp_OASetProperty” stored procedure.

The next section (Section E) processes through all steps. Because this section processes through the step information exactly as it does the tasks, I will not go over Section E. To see how I process through the step information, you can look at the entire script to see the code.

Lastly, I save the changed package on the new server. This is a two-step process. Here is the code for 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

The first step of this process uses the “RemoveFromSQLServer” method to remove the package being changed from my target server (Server2). In case the package already exists on the target server, using this method ensures that I will copy the package, rather than add a new version. (Note that I test for two different error messages being returned from the execution of the “RemoveFromSQLServer” method. A return code of “-2147217900” indicates the package being copied does not exist on the target server.) Next, I save the package on the target server by using the “SaveToSQLServer” method. The last bit of code shown is the code to display errors returned when executing the different OLE Automation stored procedures.

When I run the complete script just described against my two DTS packages, I get the following output. This output shows which DTS package properties where changed:

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

The two packages I copied were very simple packages that did not have all the possible collection and properties that might be found in your DTS Packages. So when using my code to migrate your DTS packages, you might need to process through additional collections, and/or look for different properties to change.

Using SQL-DMO and the process I described allows me to quickly modify and copy packages from one server to another. To copy DTS packages from your development server to your production server but wish to avoid the frustration of having to change the connection string information, or other properties, each time you copy a package, then a more automated approach like the one I’ve described will streamline your DTS package migration efforts.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different Web publishing sites, as well as SQL Server Magazine. Greg also maintains a Website that contains a number of SQL Server examples to common DBA and SQL developer issues.


Contributors : Gregory A. Larsen
Last modified 2005-04-12 06:21 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone