Post reply

dts package steps fail when run from sql analyser

  • September 18, 2007 at 2:37 pm

    #120489

    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

  • September 18, 2007 at 2:47 pm

    #734412

    When you run the package thru SQL, the package runs on whatever machine is running the SQL Server, with the security rights of the SQL Server and all OS references are from the point of view of SQL Server.

    So if the package references a H drive and the SQL Server machine does not have a H drive, an error will occur.

    P.S.

    Please do not execute package from SQL !! Most companies will not allow any operating system command to be run from SQL including any extended stored procedures or any of the Object Automation procedures. Under SQL Server 2005, most of these commands are disabled by default. Do this the correct way and write a program or script to run a package.

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply