July 25, 2002 at 4:37 pm
Yes, it does. Anything that is part of the package gets moved.
Andy
July 25, 2003 at 12:18 pm
any suggestions on how to move dts packages from server a to server b where server a and b are totally disconnected from each other?
thanks!
-nicky
July 25, 2003 at 1:18 pm
No way to establish a temporary connection over a VPN? You can export to COM files, copy them to the other server that way. Or even export as VB code and run that against the new server. Other than that...you could BCP out the tables involved, BCP back in on the other server.
Andy
October 24, 2003 at 10:52 am
What platform do you code this in? I'm doing a project for school where I have to copy info from one server to another. Also does there need to be a main routine to pass the values through the function(i.e. Destination Server)?
October 24, 2003 at 3:07 pm
Personally I find copying the entries in the sysdtspackages table a lot easier, as described at http://www.sqldts.com/?204
Steven
October 29, 2003 at 6:46 pm
Vb6.
Steven, copying the table is an option (and not a bad one), sometimes I just like to explore - never know what may turn out to be useful.
Andy
May 5, 2006 at 12:52 am
I knocked this script up to copy either all or a set of listed dts packages from one server to another. If it already exists on the destination server it is deleted and replaced otherwise it is simply created. My convention for portability is to have a global variable set to the server on which the package is running. This script will also update the global variable as it is saved on the destination server. If the variable name can't be found it will simply continue.
Save the script contents to a file called something like copydts.vbs
Then from the command prompt run it using
cscript copydts.vbs /svr_src SRCSERVER /svr_dest DESTSERVER
for integrated security or specify the sql security username and password with the parameters in the script.
Hope this helps someone.
Option Explicit
'===============================================================================
'- Copy listed or all DTS packages from one SQL Server to another
'
'============================================================================================
Dim SQL_SRC_SERVERNAME, _
SQL_SRC_USERNAME, _
SQL_SRC_PASSWORD, _
SQL_DEST_SERVERNAME, _
SQL_DEST_USERNAME, _
SQL_DEST_PASSWORD
'Command Line Arguments
SQL_SRC_SERVERNAME = GetArgs( "svr_src", "(local)" )
SQL_SRC_USERNAME = GetArgs( "sqlu_src", "NULL" ) ' standard sql security username. NULL if integrated.
SQL_SRC_PASSWORD = GetArgs( "sqlp_src", "NULL" ) ' standard sql security password. NULL if integrated.
SQL_DEST_SERVERNAME = GetArgs( "svr_dest", "(local)" )
SQL_DEST_USERNAME = GetArgs( "sqlu_dest", "NULL" ) ' standard sql security username. NULL if integrated.
SQL_DEST_PASSWORD = GetArgs( "sqlp_dest", "NULL" ) ' standard sql security password. NULL if integrated.
' DTS Constants
Const DTSSQLStgFlag_UseTrustedConnection = 256
Const DTSSQLStgFlag_Default = 0
WScript.Echo Now & " Started scripting DTS packages from " & SQL_SRC_SERVERNAME & " to " & SQL_DEST_SERVERNAME
Dim dtsApp ' As New DTS.Application
Dim dtsPackage ' As DTS.PackageSQLServer
Dim dtsPackageDest ' As DTS.PackageSQLServer
Dim dtsPackages ' As DTS.PackageInfos
Dim dtsInfo ' As DTS.PackageInfo
Dim packagesToCopy
Set packagesToCopy = WScript.CreateObject("Scripting.Dictionary")
With packagesToCopy
' .Add "ALL", ""
.Add "IDL - Export", ""
.Add "IDL - Import", ""
.Add "IDL - Staging To Live - ALL", ""
.Add "NETWORK - Update", ""
End With
Set dtsApp = CreateObject("DTS.Application")
If SQL_SRC_USERNAME = "NULL" Then
Set dtsPackage = dtsApp.GetPackageSQLServer(SQL_SRC_SERVERNAME, "", "", DTSSQLStgFlag_UseTrustedConnection)
Else
Set dtsPackage = dtsApp.GetPackageSQLServer(SQL_SRC_SERVERNAME, SQL_SRC_USERNAME, SQL_SRC_PASSWORD, DTSSQLStgFlag_Default)
End If
Set dtsPackages = dtsPackage.EnumPackageInfos("", True, "")
For Each dtsInfo In dtsPackages
If packagesToCopy.Exists(dtsInfo.Name) Or packagesToCopy.Exists("ALL") Then
WScript.Echo Now & " Copying " & dtsInfo.Name
Set dtsPackage = CreateObject("DTS.Package")
If SQL_SRC_USERNAME = "NULL" Then
dtsPackage.LoadFromSQLServer SQL_SRC_SERVERNAME, "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", dtsInfo.Name, Nothing
Else
dtsPackage.LoadFromSQLServer SQL_SRC_SERVERNAME, SQL_SRC_USERNAME, SQL_SRC_PASSWORD, DTSSQLStgFlag_Default, "", "", "", dtsInfo.Name, Nothing
End If
On Error Resume Next
dtsPackage.GlobalVariables("ServerName") = SQL_DEST_SERVERNAME
If Err.Number 0 Then
Err.Clear
Else
WScript.Echo Now & " Set global variable IDLServerName to " & SQL_DEST_SERVERNAME
End If
Set dtsPackageDest = CreateObject("DTS.Package")
If SQL_DEST_USERNAME = "NULL" Then
dtsPackageDest.LoadFromSQLServer SQL_DEST_SERVERNAME, "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", dtsInfo.Name, Nothing
If Err.Number = 0 Then
WScript.Echo Now & " Deleting " & dtsInfo.Name & " from " & SQL_DEST_SERVERNAME
dtsPackageDest.RemoveFromSQLServer SQL_DEST_SERVERNAME, "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", dtsInfo.Name
Else
Err.Clear
End If
dtsPackage.SaveToSQLServer SQL_DEST_SERVERNAME, "", "", DTSSQLStgFlag_UseTrustedConnection
Else
dtsPackageDest.LoadFromSQLServer SQL_DEST_SERVERNAME, SQL_DEST_USERNAME, SQL_DEST_PASSWORD, DTSSQLStgFlag_Default, "", "", "", dtsInfo.Name, Nothing
If Err.Number = 0 Then
WScript.Echo Now & " Deleting " & dtsInfo.Name & " from " & SQL_DEST_SERVERNAME
dtsPackageDest.RemoveFromSQLServer SQL_DEST_SERVERNAME, SQL_DEST_USERNAME, SQL_DEST_PASSWORD, DTSSQLStgFlag_Default, "", "", dtsInfo.Name
Else
Err.Clear
End If
WScript.Echo Now & " Saving " & dtsInfo.Name & " to " & SQL_DEST_SERVERNAME
dtsPackage.SaveToSQLServer SQL_DEST_SERVERNAME, SQL_DEST_USERNAME, SQL_DEST_PASSWORD, DTSSQLStgFlag_Default
End If
End If
On Error Goto 0
Next
WScript.Echo Now & " Finished scripting DTS packages from " & SQL_SRC_SERVERNAME & " to " & SQL_DEST_SERVERNAME
'-----------------------------------------------------
Function GetArgs( sSwitch, sDefaultValue )
'-----------------------------------------------------
' Checks the command line arguments for a given switch and returns the associated
' string, if found. If not found, the defaultValue is returned instead.
dim ArgCount, bMatch
ArgCount = 0
bMatch = 0
do while ArgCount < WScript.arguments.length
if Eval((WScript.arguments.item(ArgCount)) = ("-" + (sSwitch))) Or Eval((WScript.arguments.item(ArgCount)) = ("/" + (sSwitch))) then
bMatch = 1
Exit do
else
ArgCount = ArgCount + 1
end if
Loop
if ( bMatch = 1 ) then
GetArgs = ( WScript.arguments.item(ArgCount + 1) )
else
GetArgs = ( sDefaultValue )
end if
End Function
May 5, 2006 at 1:58 am
Chris
Best Regards,
Chris Büttner
May 5, 2006 at 5:34 am
I've developed a few dts packages for our customers, and the delivery mechanism I have used for the dts package is: while in the designer, goto the "Save As..." menu, change the "location" from "SQL Server" to "Structured Storage file". A filename is suggested, you specify the location for that file to be generated, then hit "OK". Then you have a *.dts file that you can email, ftp, zip up (if large), etc. You put that file onto the destination server.
To get it into the destination SQL Server, Right-click on the "Data Transformation Services" folder in Enterprise Manager and select "Open Package" then find and select the saved .dts file. It will open it up into the Designer. Before you start making any changes goto the Save As menu once more, this time choose "SQL Server" for its location and save it to the local SQL Server.
Of course after all this, like all other transfer methods, you will have to change all of the content of the DTS that has specific references to the local SQL Server that may be different.
May 5, 2006 at 6:38 am
Here is another approach that we have used internally.
http://www.sqlservercentral.com/columnists/bszabo/adtsdatastore.asp
http://www.sqlservercentral.com/columnists/bszabo/restoreadtspackagefromadtsstore.asp
May 5, 2006 at 7:10 am
I used the steps as set out by http://www.sqldts.com and they worked great.
Just wondering, is there any way of amending the connections within the dts packages without having to modifiy each DTS package seperatly?
Cheers
May 5, 2006 at 7:50 am
I often copy packages from our QA to Production servers and have been looking to use a way to do it that is dependable. Normaly I just save to a flat file and move the file over to prod server, re-open it, then save it to SQL Server. I had used the freeware tool "DTSBackup2000" numerous times in the past until on a couple of complex packages the layout and some info within the package "changed". Since then I have been leery about using it and have stuck with the file save method.
Have you had any experience with this tool? Are there any other tools available that perform this same function?
Thanks!!
-Mike
May 5, 2006 at 11:03 am
I just normally restore the MSDB from the source to the destination server whenever I have to migrate to a new server. But yes, it also copies all the other stuff including jobs and maintenance plans which you may not need on the destination server.
May 5, 2006 at 11:36 am
Thanks for showing how one could do this programatically.
An alternative approach that I find useful is here: http://www.sqldts.com/default.aspx?204
Cheers.
dj
May 5, 2006 at 3:46 pm
Andy,
This is cool. Good article.
Did someone try to restore MSDB as another database (I did that) , rename the syspackages table (I did not do that yet) and get data this way by DTS?
I also will be interested if someone will make the same tracing with Save As VBscript File and code a loop that will create one or many files with code for all packages in VBscript. Then it will be easier to do Replace on the server name, share names, IPs and other changable things.
Yelena
Regards,Yelena Varsha
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply