How to save/load DTS package in a scheduled job?

Question

How to save/load DTS package in a scheduled job?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
July 17, 2007 at 1:19 pm

#174508

I would like to save/load a DTS package in a scheduled job into/from a structured storaged file.
Any input will be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You can only execute a package in a job. Use DTSRun in a job step making sure you include the 'F' argument to specify the file path.

Ex: dtsrun /Ffilename /Npackage_name

Greg

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

If the package has multiple versions you have to specify the version number tooo.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

The processes of saving/loading DTS packages can be easily carried out. What I would like to do are to schedule such saving/loading as scheduled jobs.

Thank you all for your kind inputs.

noeld SSC Guru Points: 96590 More actions · Answer 4

You will need to use VBSCRIPT and use DTS Object model to save/load the package. Use VBSCRIPT subsystem of SQLSERVERAGENT.

* Noel

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Sorry SQL ORACLE, I completely misunderstood your question! Luckily, Noel knows what your trying to do.

Greg

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 6

Noel,

Sounds interesting. Can you point us to where we can see some examples, articles, posts, etc. of this? Thanks.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

Here is one link.

http://www.databasejournal.com/features/mssql/article.php/3417651

More inputs will be welcome.

Jeff Gray SSChampion Points: 10667 More actions · Answer 8

Hey, Carlos!

What the oracle has done is point you to an example of using the COM for DTS packages. To understand this a little better, you might want to look at the msdn site.

http://msdn2.microsoft.com/en-US/library/aa176226(SQL.80).aspx

This location documents the whole mess that is DTS. (That mess which I so dearly love) The first link on the page goes right into the object model.

You might be able to do what you want directly in T-SQL using the OA_ procedures, but you're probably better of writing it outside of T-SQL in VB6.

Good luck!

jg