Running a DTS Package Through TSQL with a COM Concept

What if you wanted to use a COM object through TSQL Statement? SQL Server facilitates this task by provding a few system sorted procedures like sp_OACreate, sp_OAMethod, etc.

Check out the example below for how to execute a DTS package. It includes declaration, creation of an object, and uses the object methods "LoadfromStorageFile" and "Execute."


--Declaring variable
DECLARE @objdts int
 
--Creating object
EXEC sp_OACreate 'DTS.Package', @objdts OUTPUT
 
--Loading DTS storage file to object
EXEC sp_OAMethod @objdts, 'LoadFromStorageFile', NULL, 'C:\MyDTS.dts', ''
 
--Executing DTS package through an object
EXEC sp_OAMethod @objdts, 'Execute'

K.Nageswara Rao

Submit a Tip

Browse "Java" Tips

Browse All Tips

0 Comments (click to add your comment)

Comment and Contribute