Copying DTS Packages To a Different Server

,

We get a LOT of interesting questions in our discussion area. One recent

question was about how to copy a DTS package from one server to another. Seemed

like something worth investigating!

DTS packages can be saved to SQL Server which is the most common, to Visual

Basic code, to a COM structured file, or to the repository. The default is SQL

Server and that's the one I'm to going to focus on in this article. Packages get

saved in MSDB to a table called sysdtspackages, which has the following schema:

Here is some sample data for a small package I created:

As you can see the combination of the ID and the versionid make up the

primary key. Each time you save the package a new row will be added. So when you

design the package, how does it know which is the "current" one? A

quick test revealed that if you right click a package and select execute it uses

the one with the latest createdate. If you want to edit an older version, you

can always do so by selecting versions from the right click menu, then selecting

the one you want to edit:

My first thought was that since the data is stored in a table, let's use DTS

to move the DTS packages! I set up a second named instance of SQL on my machine,

right clicked MSDB and All Tasks, Import Data, then stepped through the wizard

using until I got to the form where you select the tables to copy:

It's not listed! Apparently you can't copy system tables. Ok, kinda makes

sense. My first thought was to just pick a table and save the package, then edit

it to copy the table. Not exactly what I had in mind, but could be made to work.

My next thought was BCP. No reason this shouldn't work, just not as simple to

set up, though maybe a good script or simple front end would fix that. Let's

keep looking and see what the other options are.

The DTS Designer has "save as" functionality that lets you save a

package to a different server. Not what I'd what to do for a couple hundred

packages, but certainly good enough for a few. But how does the Designer do it?

Profiling the source server showed a call to sp_get_dtspackage, checking the

source of that proc revealed it just does some security checking then returns

the row as a recordset. It also calls sp_enum_dtspackages which as you would

expect returns a recordset of packages meeting the criteria passed to it. I then

repeated the "save as" this time tracing the second server. Only one

proc gets called, sp_add_dtspackage, which takes a number of parameters that

match the table schema. Seems like I could do this easily enough from VB. 

The solution I posted initially in response to the question was to use VB to

access the DTS package via the COM interface to do the "save as". Here

is a touched up version of the code I posted:

Sub MoveDTSPackages(SourceServer As

String, DestinationServer As String)

'3/6/02 Andy Warren

'Code to copy DTS packages using the "save as" functionality

built into DTS

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim oPackage As DTS.Package

On Error GoTo Handler

If SourceServer = "" Or

DestinationServer = "" Then

MsgBox "Must provide both source and destination server names."

Exit Sub

ElseIf UCase$(SourceServer) = UCase$(DestinationServer) Then

MsgBox "Source and destination server names cannot be the same."

Exit Sub

End If

'establish a trusted connection to source

server

Set cn = New ADODB.Connection

cn.CursorLocation = adUseClient

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Initial Catalog=MSDB;Data Source=" &

SourceServer

'iterate through all sql packages, open

and then save to the destination server

Set rs = cn.Execute("select * from msdb..sysdtspackages")

Do Until rs.EOF

Set oPackage = New DTS.Package

With oPackage

 .LoadFromSQLServer SourceServer, , ,

DTSSQLStgFlag_UseTrustedConnection,,,,rs.Fields("name")

 .SaveToSQLServer DestinationServer, , , DTSSQLStgFlag_UseTrustedConnection

rs.MoveNext

Loop

'clean up

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

Exit Sub

Handler:

MsgBox Err.Description

Resume Next

End Sub

There are a couple problems with this approach. The first is that when you

use the object model to copy the package, all text annotations are lost. Bug or

feature? The second is that it could potentially cause the wrong package to

become the "default" package since we are not specifying the order of

the select. Easy enough to change the select to order by createdate, but we

still lose the text annotations. Maybe a third issue is whether you would really

want to copy all versions to the other server or just the latest one? 

I then modified my code to use ADO to run sp_add_package as listed below.

This did preserve the text annotations in my test package as expected.

Sub MoveDTSPackages2(SourceServer As String, DestinationServer As String)

'3/16/02 Andy Warren

'Code to copy DTS packages using ADO to move the data, a binary copy

Dim cnSource As ADODB.Connection

Dim cnDestination As ADODB.Connection

Dim rs As ADODB.Recordset

On Error GoTo Handler

If SourceServer = "" Or DestinationServer = "" Then

MsgBox "Must provide both source and destination server names."

Exit Sub

ElseIf UCase$(SourceServer) = UCase$(DestinationServer) Then

MsgBox "Source and destination server names cannot be the same."

Exit Sub

End If

'establish a trusted connection to source server

Set cnSource = New ADODB.Connection

cnSource.CursorLocation = adUseClient

cnSource.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MSDB;Data Source=" & SourceServer

'another connection for destination server

Set cnDestination = New ADODB.Connection

cnDestination.CursorLocation = adUseClient

cnDestination.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MSDB;Data Source=" & DestinationServer

'iterate through all sql packages

Set rs = cnSource.Execute("select * from msdb..sysdtspackages order by createdate")

With rs

Do Until .EOF

Call AddDTSPackage(cnDestination, .Fields("Name"), .Fields("ID"), .Fields("VersionID"), .Fields("Description"), .Fields("CategoryID"), .Fields("Owner"), .Fields("PackageData"), .Fields("PackageType"))

.MoveNext

Loop

End With

'clean up

rs.Close

Set rs = Nothing

cnSource.Close

Set cnSource = Nothing

cnDestination.Close

Set cnDestination = Nothing

Exit Sub

Handler:

MsgBox Err.Description

Resume Next

End Sub

Sub AddDTSPackage(cn As ADODB.Connection, PackageName As String, ID As String, VersionID As String, Description As String, CategoryID As String, Owner As String, PackageData As String, PackageType As Long)

Dim cmd As ADODB.Command

Dim params As ADODB.Parameters

Set cmd = New ADODB.Command

' Set command properties

With cmd

Set .ActiveConnection = cn

.CommandText = "sp_add_dtspackage"

.CommandType = adCmdStoredProc

Set params = .Parameters

End With

' Define stored procedure params and append to command.

params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)

params.Append cmd.CreateParameter("@name", adVarWChar, adParamInput, 128)

params.Append cmd.CreateParameter("@id", adGUID, adParamInput, 0)

params.Append cmd.CreateParameter("@versionid", adGUID, adParamInput, 0)

params.Append cmd.CreateParameter("@description", adVarWChar, adParamInput, 255)

params.Append cmd.CreateParameter("@categoryid", adGUID, adParamInput, 0)

params.Append cmd.CreateParameter("@owner", adVarWChar, adParamInput, 128)

params.Append cmd.CreateParameter("@packagedata", adVarBinary, adParamInput, 2147483647)

params.Append cmd.CreateParameter("@packagetype", adInteger, adParamInput, 0)

' Specify input parameter values

params("@name") = PackageName

params("@id") = ID

params("@versionid") = VersionID

params("@description") = Description

params("@categoryid") = CategoryID

params("@owner") = Owner

params("@packagedata") = PackageData

params("@packagetype") = PackageType

' Execute the command

cmd.Execute , , adExecuteNoRecords

'done

Set cmd = Nothing

End Sub

I think it's safe to assume that BCP would work equally well, or even TSQL

doing an insert using a linked server. Of course even a successful copy to the

new server doesn't guarantee it will work correctly - you still may need to

change paths, servernames, passwords, create folders, or even add other external

support files to the server. I don't see an easy way to do that in an automated

fashion.

If you're new to DTS our own Brian

Knight co-authored a book on the subject which Steve Jones has reviewed.

Steve also has an article about saving

packages using COM storage rather than on the server you might want to read.

Questions or comments?

Rate

3 (1)

Share

Share

Rate

3 (1)