Executing a dts using T-SQL

[GJP55]

Could anybody see why my code will not execute a dts.

I am changing a global variable which looks ok but the execute part of the code can not execute the DTS. The code below is what I am using and it does not return any errors when ran.


declare @hr int, @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-------------------Set the Package--------------------------
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("MyServer", "", "", 256, , , , "ExcelFileTest")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Execute the package
EXEC @hr = sp_OAMethod @oPKG, 'Execute'

 

[nigelrivett]

See

http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

What you are doing looks ok except you aren't settting a global variable. If that's all you need to do why not use dtsrun which allows you to set global variables.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.