Post reply

Calling dts package from stored procedure

  • April 19, 2006 at 2:09 am

    #114280

    Hi

    We worked in sql 2000 with a stored procedure that calls a dts package.

    Now we have created a ssis package and a new stored proecedure, but it will not work.

    Command line is like this :

    exec master..xp_cmdshell 'DTEXEC /U sa /P dvcsepia /Ser SQLSEPIA /SQ "MSDB Import excel file verdelingen in FORD" /de ""'

    or

    exec

    xp_cmdshell 'dtexec /file "E:\SSIS Packages\Import excel file verdelingen in FORD.dtsx"'

     

    We receive an ole db error :

    Class not registered.

    What can this be ?

    Thx in advance

    El Jefe

     

     

     


    JV

  • April 19, 2006 at 9:15 am

    #633076

    Can this be an issue becoz we are using the 64bit version and mdac is only 32bit ?


    JV

  • April 20, 2006 at 6:28 am

    #633229

    Why not create a SQL Agent job and then run it from sp_start_job?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 20, 2006 at 7:35 am

    #633256

    Becoz whe have the same issue using a job.


    JV

  • April 21, 2006 at 6:14 am

    #633479

    Hmmm... That honestly sounds like a security problem, but its hard to know. Sorry I wasn't much help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 26, 2006 at 7:43 am

    #634286

    Hola amigo, esto lo he utilizado para ejecutar un dts desde un procedimiento almacenado, espero que te sirva. Debes declarar todas las variables e inicializarlas con los datos que se requieran, si has utilizado la sentencia LoadFromSQLServer te va a resultar muy facil.

    -- Crea un objeto Pkg

    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT

    -- Evaluas la seguridad y construyes la sentencia LoadFromSQLServer

    para cargar el DTS

    IF @IntSecurity = 0

     SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'

    ELSE

     SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

    EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

    -- Ejecutas el DTS

    EXEC @hr = sp_OAMethod @oPKG, 'Execute'

  • April 26, 2006 at 7:50 am

    #634289

    Hi sorry but no habla Espanol

    Like the country though 🙂


    JV

  • November 13, 2006 at 8:53 am

    #671381

    ok..so maybe you don't habla espanol...but the answer is in the sql, and sql is the universal language ;=)

    As for the spanish, you could have made an effort!  Below is my approximate translation.

    Hello my friend, here is the code to execute a dts from a stored proc, I hope you'll find it handy.  You have to declare all the variables and initialise them with data where necessary.  If you've used LoadFromSQLServer, then it's really easy.

  • June 25, 2008 at 11:05 am

    #834151

    but that didn't solve the problem of trying to execute an SSIS from an stored procedure, did it?

    I am using a version of the code but it just wouldn't work :S

Viewing 9 posts - 1 through 8 (of 8 total)

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