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'
