Hi
when I try to run dts packages (all dts packages in a server) from Entreprise manager, they work fine. but when I try to run them from sql analyser (T-sql), some steps fail in the package. the only steps that succeed are the ones that are not preceeded by excel connection tasks.
so whenever there is a step that has excel connection in it, it fails.
any help will be appreciated
here is the code that I'm using to execute the package:
declare @hr as int, @oPKG int, @cmd varchar(255)
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
select @hr
SET @Cmd = 'LoadFromSQLServer("' + @@SERVERNAME +'", "", "", 256, "", , , "package name")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
select @hr
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
EXEC @hr = sp_DisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG