June 2, 2003 at 11:52 am
Does anyone know an easy way to copy DTS jobs between servers. We use SQL2000 SP2.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
June 2, 2003 at 11:50 pm
If you want exact copy and as long as you can see in EM both servers:
1. Setup DTSTask_DTSDataPumpTask with source MSDB database on the source server - destination MSDB database on the destination server.
In source Tab you can type in sql query window a select statement with where condition of your choice which will limit the recordset to those packages which you want to transfer
eg
Select * from sysdtspackages
where <ConditionYouWish2Choose>
eg WHERE name like 'PROD%' - will transfer all packages and all version of the packages whosename start with 'PROD'
select any table from the list.
define any valid transformation. so you can save the Task in EM DTS designer.
Then in disconected edit - you change manually DestinationObjectName property of the DataPumpTask (as you actually cannot select sysdtspackages table from the list) to be: [msdb].[dbo].[sysdtspackages]
forcing the proper assignment of this property
Then come back to editing the Task
choose the Transformation Tab - it will tell you that some transformation are now invalid - choose remove all transformation and redo auto-mapping - click on OK.
Now you can edit your transformation if needed. The automapping will create 10 copy column transformation.
You are ready to go. Do what you normally do with your packages before you run it.
Just execute the package - as long as you do not run it twice - it cannot save the package again (with the same properties ID and Version_ID - they remain the same as long as you won't manipulate them in the TransformTask - i.e you have not replaced the copy column transformations with one ActiveX one - which you can do to further manipulate the process.
Make sure all columns are copied.
I used this approach few times and it works ( I mean you have transfered the packages - if they work there - it's another matter).
This will create exact copy of the records from one msdb server to another - including
id and version_id
And I created one such package while I was writing this response.
Cheers
Tom
June 3, 2003 at 10:21 am
Once they're stored in MSDB, this has always sounded to me like an obvious way to do so.
I want to copy some DTS jobs from a development server to a production server and I may be forced to use it the way you specified. But what if I somewhat want to document the feature or save it somewhere else so I can re-import it anywhere else and editr it if I need to...
Is there a way to do so?
June 3, 2003 at 5:36 pm
Here is an easier procedure I found in technet:
Step 5: How to Move DTS Packages
Step 5 is optional. If DTS packages are stored on the source server in the SQL Server or the repository, you can move them if you want. To move DTS packages between servers:
Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.
Save the package on the destination server to the SQL Server, or to the repository.NOTE: You have to move each package one by one in separate files.
-or-
Open each DTS package in the DTS Designer.
On the Package menu, click Save As.
Specify the destination SQL Server.
NOTE: The package may not run properly on the new server. You may have to edit the package, and change any references in the package to connections, files, data sources, profiles and other information located on the old source server, to reference the new destination server. You must make these changes on a package by package basis based on the design of each package.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
June 3, 2003 at 9:15 pm
The good thing about my procedure is that I can save it as a package.
I can repeat the process (after modifying it if necessary) to:
copy to another server
move another set of packages.
Also it is fast and moving let say 100 packages will take not much more time than moving 2 or 5.
And I do not loose the annotations nor the layout of GUI image of the tasks and workflow.
Cheers
Tom
June 4, 2003 at 10:29 am
I would be happy to try your solution, but I don't understand it. Guess I'm not advanced enough. Would you resubmit it in layman's terms.
"I hope you enjoy your retirement as much as I will."
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
June 6, 2003 at 7:42 am
Check out sqldts.com
John Deupree
June 6, 2003 at 7:52 am
Did you look at BOL? There are a couple of items to check there.
1) Under "Managing DTS Package Programs" there is a section at the bottom titled "Saving and Loading Packages" that looked plausible and probably a bit like your technet advice.
2) There also appears to be a method you might employ, LoadFromSQLServer.
I believe when the DBAs have done it here for me they have employed the "Save As" / "Open" method to move the package.
With any method you typically do end up tweaking connections... as you noted.
Good luck
June 6, 2003 at 10:27 am
That website is extremely helpful. Thanks.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
June 6, 2003 at 11:37 am
Use 2nd procedure stated by (fizzleme)
Steps:
1. Expand Data Transformation tab in Enterprise manager (source server)
2. Highlight local packages
3. Select the desired package, right click and select design package.
4. From the package menu select save as
5. Set location to SQL server
6. Set Server to the destination Server name
7. If you want you can rename your package or keep the same name and save it
MW
MW
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply