SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Copying DTS Packages To a Different Server / Latest Posts

RE: Copying DTS Packages To a Different Server

Taylor Geisse — Tue, 27 Feb 2007 11:43:00 GMT

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_SuccessEnd FunctionSub 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 SubEnd SubSub 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 , , adExecuteNoRecordsEnd Sub

RE: Copying DTS Packages To a Different Server

David F. Straker — Fri, 20 Oct 2006 07:35:00 GMT

Hi AllFurther 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.

RE: Copying DTS Packages To a Different Server

dharper3 — Mon, 03 Jul 2006 04:21:00 GMT

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.

RE: Copying DTS Packages To a Different Server

sandipanghosh — Tue, 23 May 2006 20:55:00 GMT

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?

RE: Copying DTS Packages To a Different Server

Venu Dukka — Fri, 12 May 2006 15:52:00 GMT

The easiest way according to me is to transfer them directly like:

-- All packagesinsert into msdb.dbo.sysdtspackesselect *from remote_server.msdb.dbo.sysdtspackageswhere (blah.. blah) OR -- Latest versions of packagesinsert into msdb.dbo.sysdtspackesselect 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.versionidwhere (blah.. blah)

Are there any disadvantages doing this kind of transfer?

Venu

RE: Copying DTS Packages To a Different Server

Mark Taylor-212571 — Wed, 10 May 2006 03:40:00 GMT

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.

RE: Copying DTS Packages To a Different Server

James Horsley — Tue, 09 May 2006 13:19:00 GMT

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 ...

RE: Copying DTS Packages To a Different Server

Loner — Mon, 08 May 2006 10:14:00 GMT

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.

RE: Copying DTS Packages To a Different Server

osama-240238 — Mon, 08 May 2006 08:30:00 GMT

what about this easy tool which called dtsbackup2000

http://www.sqldts.com/default.aspx?272

RE: Copying DTS Packages To a Different Server

Peter De Ceulaer — Fri, 05 May 2006 23:22:00 GMT

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 server7) copy from temp table to sysdtspackages.4 through 7 are combined in an import DTSThis process maintains text annotations and lay-out.That's working well for me at this point...Grtz, Pete

RE: Copying DTS Packages To a Different Server

Yelena Varshal — Fri, 05 May 2006 15:46:00 GMT

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

RE: Copying DTS Packages To a Different Server

Dexter Jones — Fri, 05 May 2006 11:36:00 GMT

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.

RE: Copying DTS Packages To a Different Server

Kedar Potdar — Fri, 05 May 2006 11:03:00 GMT

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.

RE: Copying DTS Packages To a Different Server

chubbsm — Fri, 05 May 2006 07:50:00 GMT

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

RE: Copying DTS Packages To a Different Server

sqlrumble — Fri, 05 May 2006 07:10:00 GMT

I used the steps as set out by 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

RE: Copying DTS Packages To a Different Server

cliffb — Fri, 05 May 2006 06:38:00 GMT

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

RE: Copying DTS Packages To a Different Server

John Bowman-237558 — Fri, 05 May 2006 05:34:00 GMT

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.

RE: Copying DTS Packages To a Different Server

Christian Buettner-167247 — Fri, 05 May 2006 01:58:00 GMT

Hello,
I personally prefer the following method:
Import Data->Select source server and msdb -> select destination server and msdb -> Use a query
to specify the data to transfer -> SELECT * FROM sysdtspackages [where xyz] -> Type sysdtspackages as Destination object -> Run

The only cosmetic issue will be that the creation of the sysdtspackages table on the destination
server will fail (which makes sense as it already exists). When you save to DTS, you can remove
this extra step.

An additional method is to LoadFromSQLServer->SaveToStorageFile then
LoadFromStorageFile->SaveToSQLServer. This can be fully automated while preserving all versions
of a DTS package.
This method can be used when the SQL Servers have no direct connection. (Note that several
versions of a package and even different packages can be stored in one structured storage file.
Use "GetSavedPackageInfos" to find out how to iterate over all packages and versions stored in one
storage file. But make sure you save the various versions of a package in the correct order as
the create date and save date will not be preserved.

Cheers,Chris

RE: Copying DTS Packages To a Different Server

DanKennedy — Fri, 05 May 2006 00:52:00 GMT

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.vbsThen from the command prompt run it usingcscript copydts.vbs /svr_src SRCSERVER /svr_dest DESTSERVERfor 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 ArgumentsSQL_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 ConstantsConst DTSSQLStgFlag_UseTrustedConnection = 256Const DTSSQLStgFlag_Default = 0WScript.Echo Now & " Started scripting DTS packages from " & SQL_SRC_SERVERNAME & " to " & SQL_DEST_SERVERNAMEDim dtsApp ' As New DTS.ApplicationDim dtsPackage ' As DTS.PackageSQLServerDim dtsPackageDest ' As DTS.PackageSQLServerDim dtsPackages ' As DTS.PackageInfosDim dtsInfo ' As DTS.PackageInfoDim packagesToCopySet 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 WithSet 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 IfSet 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 NextWScript.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, bMatchArgCount = 0bMatch = 0do 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 ifLoopif ( bMatch = 1 ) then GetArgs = ( WScript.arguments.item(ArgCount + 1) ) else GetArgs = ( sDefaultValue )end ifEnd Function

RE: Copying DTS Packages To a Different Server

Andy Warren — Wed, 29 Oct 2003 18:46:00 GMT

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.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Copying DTS Packages To a Different Server

Steven. — Fri, 24 Oct 2003 15:07:00 GMT

Personally I find copying the entries in the sysdtspackages table a lot easier, as described at http://www.sqldts.com/?204

RE: Copying DTS Packages To a Different Server

dpbrown — Fri, 24 Oct 2003 10:52:00 GMT

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)?

RE: Copying DTS Packages To a Different Server

Andy Warren — Fri, 25 Jul 2003 13:18:00 GMT

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.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Copying DTS Packages To a Different Server

nickyvb — Fri, 25 Jul 2003 12:18:00 GMT

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

RE: Copying DTS Packages To a Different Server

Andy Warren — Thu, 25 Jul 2002 16:37:00 GMT

Yes, it does. Anything that is part of the package gets moved.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Copying DTS Packages To a Different Server

hussaini — Thu, 25 Jul 2002 11:32:00 GMT

Does this VB script to move DTS, moves owner id along with the package, because when I use save asuse my userid, and then the developer cannot use it.ThanksAnwar

RE: Copying DTS Packages To a Different Server

Lonnie Wimble — Thu, 04 Apr 2002 06:48:00 GMT

Having experienced migrating a Data Warehouse from SQL 7.0 to SQL 2000 (a MUST, believe me!), I discovered some very interesting issues with the DTS packages. You will discover that if you attempt to migrate DTS packages from one SQL2000 server to another, you will NOT be able to edit or delete the DTS packages if the new server has a different name....Seems that rather than embed Local in the msdb entries, it hard codes the server names into the entries...Not pretty.

RE: Copying DTS Packages To a Different Server

darren.green — Tue, 02 Apr 2002 07:18:00 GMT

Just remember that UDLs are only usefull in SQL 2000 because in SQL 7.0 they where consumed at design-time not run-time.

RE: Copying DTS Packages To a Different Server

Andy Warren — Wed, 20 Mar 2002 20:23:00 GMT

Thanks for the feedback. UDL's are handy, not just for DTS packages. Every little bit helps later on.Andy

RE: Copying DTS Packages To a Different Server

pochreyes — Wed, 20 Mar 2002 19:53:00 GMT

Great article.Let me just add my thoughts on minimizing having to edit your dts packages when you move them to a different server. One way to avoid the need to change all those server connections in dts packages when you move them to another server is to use UDLs. you can setup UDLs in a centtral repository, say a file server and access them from that location. just make sure that you check the "always read properties from UDL file" check box.

RE: Copying DTS Packages To a Different Server

Andy Warren — Wed, 20 Mar 2002 16:33:00 GMT

Aint that the truth!Andy

RE: Copying DTS Packages To a Different Server

retreif — Wed, 20 Mar 2002 16:25:00 GMT

I sometimes think only in my world too, I never know what to expect in the package I develop in and maintain. Too many different "styles" to count on any thing working like that. I'm also glad not to be working in app with 200 or more DTS packages transferring data between data sources!

RE: Copying DTS Packages To a Different Server

Andy Warren — Wed, 20 Mar 2002 13:50:00 GMT

Retreif - I agree. You either have to spend extra time (possibly anyway) when you build it to make it portable or live with the consequences if/when you have to move it. I figure you could at least do the bulk move, then edit the ones you knew you had to fix, then see whats left!Darren - thanks for the link. Should have looked there first! Fun to explore though. Have you considered participating in the beta test mentioned above?Andy

RE: Copying DTS Packages To a Different Server

darren.green — Wed, 20 Mar 2002 13:35:00 GMT

The limitation of the wizard not allowing you to transfer system tables can be overcome by building the package manually.Transferring DTS Packageshttp://www.sqldts.com/main.asp?nav=1,6,204,0

RE: Copying DTS Packages To a Different Server

retreif — Wed, 20 Mar 2002 11:39:00 GMT

I guess my point was if you have to go in to review each DTS package to check if pathing whatever is correct then file save as works as well as spending the time to get this working. I'm not saying this isn't a slick way to automate if you can be sure. Sorry I didn't express that better the first time.

RE: Copying DTS Packages To a Different Server

Antares686 — Wed, 20 Mar 2002 11:14:00 GMT

I haven't had a chance yet to test it, but it is an interesting concept for moving DTS packages that I will have to play with it. Good job."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: Copying DTS Packages To a Different Server

Andy Warren — Wed, 20 Mar 2002 11:09:00 GMT

Depends on how many you're moving doesn't it? I wrote this after someone asked for help moving more than 200 packages.Andy

RE: Copying DTS Packages To a Different Server

retreif — Wed, 20 Mar 2002 10:26:00 GMT

moving DTS packages is as easy as using save-as and picking the other server...

RE: Copying DTS Packages To a Different Server

Simon Galbraith — Mon, 18 Mar 2002 07:28:00 GMT

Red Gate Software are working on a new product to help people out in comparing and synchronizing the different settings between different SQL Servers. It will be called SQL Server Compare. It will compare DTS packages, logins, jobs and server settings. The idea is to save DBAs who have to administer and set up multiple SQL Servers a whole load of time. It should be launched before the end of April. People interested in the Beta (and in influencing what functionality we include) please contact us at: sqlservercompare@red-gate.comCheers,Simon Galbraith

Copying DTS Packages To a Different Server

Andy Warren — Sat, 16 Mar 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp>http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp