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 variableDECLARE @objdts int --Creating objectEXEC sp_OACreate 'DTS.Package', @objdts OUTPUT --Loading DTS storage file to objectEXEC sp_OAMethod @objdts, 'LoadFromStorageFile', NULL, 'C:MyDTS.dts', '' --Executing DTS package through an objectEXEC sp_OAMethod @objdts, 'Execute'
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: