transferring DTS packages between SQL servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

transferring DTS packages between SQL servers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-09 : 13:04:48

Katarina writes "Hi there, I have a problem with transferring DTS packages from one SQL server onto another one. I did the following: saved DTS package as a .dts file, transferred it accross the LAN onto another server, then right click Data Transformation Services in Enterprise Manager => All Tasks => Open Package => point to the .dts file and Open. After this it gives me an error message "The system cannot find the file specified".
What am I doing wrong?
Is there some better way to do this?"

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-09 : 13:19:36

you could just save the package to the other server, granted you have appropriate permissions.

Package -> Save As...

specify the server credentials and save it to the other server.

As for "The system cannot find the file specified" error is there anything in your DTS package that references a local file that might need to be moved to the other server?

Katarina
Starting Member

6 Posts

Posted - 2002-04-10 : 03:17:06

Thanks for the alternative way, it is really easier than saving it to a .dts file.
My problem was that I forgot to install Service Pack 3 on the other
(new) server which is installed on the server where dts packages are!
After that I didnt get any error.
Thanks!

quote:

you could just save the package to the other server, granted you have appropriate permissions.

Package -> Save As...

specify the server credentials and save it to the other server.

As for "The system cannot find the file specified" error is there anything in your DTS package that references a local file that might need to be moved to the other server?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-11 : 11:08:09

Hi there,

I had the exact same problem a while back. I did it with a few lines of VBA ....

Sub CopyDTS()

Dim rst As New Recordset
Dim DTSpkg As New DTS.Package
Dim strArr
Dim n As Integer
Dim sql As String

sql = "SELECT DISTINCT name FROM msdb.dbo.sysdtspackages"
rst.Open sql, CurrentProject.Connection

Do While Not rst.EOF
With DTSpkg
.LoadFromSQLServer "TY", , , DTSSQLStgFlag_UseTrustedConnection, , , , rst(0)
.SaveToSQLServer "MELBOURNE", , , DTSSQLStgFlag_UseTrustedConnection
End With
Set DTSpkg = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

---------------------------------------

where TY and MELBOURNE are the source and destination servers, respectively.
You need to be careful about this - make sure the connections in your DTS packages are pointing to the new server. If you use {local} instead of the server name, you shouldn't have problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources