Copying DTS Packages To a Different Server
-
At my job, I develop DTS packages on a dev server, they then need to be copied to our clients test environment. The client will, after succesful testing, do a save manually from their testing to their prod. But this means I regularly have to put 30-ish DTS's from our environment to theirs. Couple of steps :
1) In every DTS that you make, use "set dynamic properties" for defining the servers and DBs that you work on through an .ini file in a fixed location (so that the same DTS will work in another environment, not failing on cannot find server). Every DTS starts with this.
2) export DTSs from a view on sysDTSpackages that takes only the latest version into a tab delimited txt file.
3) put the txt file on the FTP.
I do 2 & 3 through an export DTS.
4)Import txt file from FTP into temp table.
5)backup the DTSs that already exist in a backup table.
6)delete existing DTSs that are also in temp table from server
7) copy from temp table to sysdtspackages.
4 through 7 are combined in an import DTS
This process maintains text annotations and lay-out.
That's working well for me at this point...
Grtz, Pete
-
what about this easy tool which called dtsbackup2000
-
I used dynamic property in my DTS Package. The connection server that used to get the data was in the dynamic property and passed thru as a parameter in the job "exec dtsrun".
So when the DBA moved from one server to another server, all they had to do was to open the DTS Package and saved as in the other server. They did not need to change another connection in the DTS package itself. I was the only SQL Server programmer in that company that knew how to do this. It saved the DBA a lot of time, however it did not seem they appreciate what I did.
They did not even tell any other SQL developers so now when they move the dts packages (a couple hundred) and had to open each one to change the connection servers. Good for them ! This is their job security.
-
DTSBackup2000 is the one for me - as it also allows you to save away copies of the jobs in its own format that preserves layout etc (not on any server) which you can then easily restore when some pillock deletes a package ...
James Horsley
Workflow Consulting Limited -
Talk about timing......
I have just created a server health check process that runs every morning, this health check is stored in a scheduled DTS package and it needed to be placed on all our SQL Server (over 20 box), this code is just what the doctor ordered.
-
The easiest way according to me is to transfer them directly like:
-- All packages
insert into msdb.dbo.sysdtspackes
select *
from remote_server.msdb.dbo.sysdtspackages
where (blah.. blah)
OR
-- Latest versions of packages
insert into msdb.dbo.sysdtspackes
select a.*
from remote_server.msdb.dbo.sysdtspackages a
join (
select [id], versionid, max(createdate)
from remote_server.msdb.dbo.sysdtspackages
group by [id], versionid
) b on a.[id] = b.[id] and
a.versionid = b.versionid
where (blah.. blah)
Are there any disadvantages doing this kind of transfer?
Venu
-
Ok it done.
I moved packages to a file which i saved into c drive. Now i want to paste it back to SQL server. How i will do ythrough DTSrun.exe ? or some other way?
-
Or you could use a tool that I have found fantastic. SQL backup2000 provides a nice GUI interface and is very handy for copying large numbers of packages. I think you can find it at SQLDTS.com and the author is Darren Green. A great tool where ou specify the source and target, all packages on the source server are identified and you can select one or hundreds to migrate to the specified destination. Supports passwords etc. Give it a go its been a very usefull tool.
-
Hi All
Further to this topic - we have a T-SQL script from the net that will take a DTS package from a structured storage file, load it into SQL Server, and modify the connection names etc. in the process.
BUT..... when we use this script to load a package, then open the loaded package up in Enterprise Manager on the new server, the layout has been wrecked. All the tasks, workflows etc. are still in place and accurate; but the various tasks have been relocated on the screen and spaced so far apart that it takes a zoom out (to about 20%) to see them all.
Does anybody know of a way to load/modify/save a DTS package this way without this automatic re-layout happening?
Dave.
-
Andy, I just want to start off by really thanking you for this script! I modified it to work in a DTS package and made a few other modifications. Maybe someone else would like to have the ability to save it inside a DTS package. This package will be deployed to update a PD server after updates on a DP server are complete.
Modifications:
- Converted to VB ActiveX Script for use inside a DTS Package on SQL 2000
- Added the reference of global variables for the server names
- Added check to copy only new versions of a package
- Check to make sure the package that migrates all the DTS packages is not copied (this can be expanded to include other ones as well.)
Thanks again!
~Taylor
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
MoveDTSPackages DTSGlobalVariables("SourceServer").Value, DTSGlobalVariables("DestinationServer").Value
Main = DTSTaskExecResult_Success
End Function
Sub MoveDTSPackages(ByVal SourceServer, ByVal DestinationServer)
'3/16/02 Andy Warren
'Code to copy DTS packages using ADO to move the data, a binary copy
'2/27/07 Taylor Geisse - Modified to run in VB ActiveX Script for use in DTS
' Constants - http://www.connectionstrings.com/adoenumerations.asp
Const adUseClient = 3
Dim cnSource
Set cnSource = CreateObject("ADODB.Connection")
Dim cnDestination
Set cnDestination = CreateObject("ADODB.Connection")
Dim rsSource
Set rsSource = CreateObject("ADODB.Recordset")
Dim rsDest
Set rsDest = CreateObject("ADODB.Recordset")
Dim sqlSourceCmdText
Dim sqlDestCmdText
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
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
cnDestination.CursorLocation = adUseClient
cnDestination.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MSDB;Data Source=" & DestinationServer)
'Build a list of packages that exist on the source server
sqlSourceCmdText = "select * from msdb..sysdtspackages order by createdate"
rsSource.Open sqlSourceCmdText, cnSource
'iterate through all sql packages on the source server
With rsSource
Do Until .EOF
'Copy only package versions that do not exist on the destination server.
'Also check to make sure that we do not copy this (and other?) packages.
sqlDestCmdText = "select COUNT(*) AS [RowCount] from msdb..sysdtspackages where [id] = '" & .Fields("ID") & "' and [versionid] = '" & .Fields("VersionID") & "'"
rsDest.Open sqlDestCmdText, cnDestination
' If a package (id/version) already exists, do not add the source package!
If rsDest.Fields("RowCount").Value = 0 And .Fields("Name") "Migration" Then
Call AddDTSPackage(cnDestination, .Fields("Name"), .Fields("ID"), .Fields("VersionID"), .Fields("Description"), .Fields("CategoryID"), .Fields("Owner"), .Fields("PackageData"), .Fields("PackageType"))
End If
rsDest.Close
.MoveNext
Loop
End With
'clean up
rsSource.Close
cnSource.Close
cnDestination.Close
Exit Sub
End Sub
Sub AddDTSPackage(ByVal cn, ByVal PackageName, ByVal ID, ByVal VersionID, ByVal Description, ByVal CategoryID, ByVal Owner, ByVal PackageData, ByVal PackageType)
' Constants - http://www.w3schools.com/ado/met_comm_createparameter.asp
Const adCmdStoredProc = 4
Const adInteger = 3
Const adVarWChar = 202
Const adGUID = 72
Const adVarBinary = 204
Const adParamReturnValue = 4
Const adParamInput = 1
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Dim params
' 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
End Sub
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply