• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Running DTS Package from SQL Job Agent

How can I run the DTSRun Utility from SQLServer Job Agent

Can u specify the syntax to use DTSRun in the SQLServer Job Scheduler?
0
neela
Asked:
neela
  • 2
1 Solution
 
sudhi022299Commented:
you can make a call to dtsrun command from the command line to execute a package sql-server.
The package could be stored either in msdb, repository or a com-structured file.

The syntax would be :
1] to execute a pkg saved in a com file would : dtsrun /Ffilename /Npackage_name /Mpackage_password
2] to execute a pkg save in the msdb: dtsrun /Sserver_name /Uuser_name /Ppassword /Npackage_name /Mpackage_password
3] to exeucte a pkg save in the repository : dtsrun /Sserver_name /Uuser_name /Rrepository_name /Ppassword /Npackage_name /Mpackage_password.

specify one of the steps of your job as type : Operating System Command (CmdExec)
and the command would be any one of the 3 syntaxes above.

Regards,
Sudhi.
0
 
neelaAuthor Commented:
How do we execute these packages from VB?
0
 
sudhi022299Commented:
Hi neela,

DTS packages could be executed from any tool which supports automation ed, vb 5 or vb 6.

From visual basic, a reference has to be made to the Microsoft DTSPackage Object Library file which is DTSPKG.RLL

Once you set the reference to it all you need to do is :

1] Instantiate an object of DTS.Package
2] Set a reference to the package by calling any of these methods LoadFromRepository, LoadFromSQLServer, LoadFromStorageFile with the appropriate parameters.
3] call the execute method of the dts package object.

Here is a sample code. I saved the package to a file ( customers.dts ) with no owner and operator password.

Private Sub Command1_Click()
 Dim objDTS As New DTS.Package
 
 Screen.MousePointer = vbHourglass
 objDTS.LoadFromStorageFile "c:\customers.dts", ""
 objDTS.Execute
 Screen.MousePointer = vbDefault
 
End Sub

Hope this answers your question.
If you need more post a comment.

Regards,
Sudhi.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now