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:
'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.
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?
To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.
We ask you to register on the site and subscribe to our newsletters. Subscribing to our newsletters gets you:
We ask that you give the newsletter a try for a week. Over 200,000 SQL Server Professionals a day find it entertaining and useful. If not, you are welcome to unsubscribe at anytime.
Steve Jones Editor, SQLServerCentral.com
