Scripting DTS packages 1

[drewj840]

Is it possible to script a DTS package so that it can be recreated on another server? I know you can output a DTS package to a structured file or to VB (6.0?) code, but I want to script the DTS package in the same way that a table or stored procedure is scripted. The purpose is to be able to run a script created on a test server on a production server and to also use this for disaster recovery. I have searched high and low and cannot find any way to do this.

 

[MDXer]

if you save it as a structured file you can open it on the new server and save it there. Thats really the only way I think of doing it.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[DLSQL]

Is your main objective to transfer DTS packages from a test server to a production server?

If so you can use DTSBackup, and it will transfer the package. I believe the tool is free. Just search in google for DTSBackup.

DL
MCDBA, MCSD, MCT, etc.

 

[bcking]

If the two servers are on the same network then you should be able to open the package from the machine it is saved on then go to PACKAGE / SAVE AS, then change the Server Name to the new server. If it isn't in the drop down box then just type it in.

If you do manage to copy your packages from production to test make sure that all of your DataSources are pointing to the correct location after copying. You may need to open each DataSource and change it from the production machine to the test one, or vice-versa.

 

[drewj840]

I don't want to have to actually go to the machine where the package is stored. This is because I am trying to write a complete script for change management that will be run by a different group. They do not (and should not have to) have the expertise to do this (although admittedly it is not a difficult task to do).

 

[diggerfm]

Howdy!
Once you've saved your package as a structured storage file, you can load it using vb like below (i have vbscript for this too, if you want it let me know)...

Dim oPkg As DTS.Package2
oPkg = New DTS.Package2()
oPkg.LoadFromStorageFile("UNC of file location", "", , , "source package name&quot

oPkg.SaveToSQLServer("target server", , ,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection)

Good luck...