MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
FAQ  
Administration  
Backup/Restore  
Connectivity  
Development  
General  
Installation  
OLAP  
Replication  
Transfer/move  
Trouble  
SQL 6.5  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 


How do I run a DTS package from the Query Analyzer or stored procedure?

Answer:

There are two ways to run a DTS package from the Query Analyzer or stored procedure:

1. By using OLE Automation objects
(run sp_OACreate, sp_OAGetProperty, sp_OAMethod and so on).

2. By using xp_cmdshell stored procedure to run dtsrun.exe file.

This is the example to execute NewPack.dts package from the disk C:

DECLARE @object int
DECLARE @hr int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
    print 'error create DTS.Package'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile',
     NULL, 'C:\NewPack.dts', ''
IF @hr <> 0
BEGIN
    print 'error LoadFromStorageFile'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
  print 'Execute failed'
  RETURN
END
See also this link:
Working with COM objects from within T-SQL


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc