Post reply

How to run ssis package by sql query without using dtexec and xp_cmdshell commands

  • October 5, 2007 at 2:54 am

    #118945

    hi ,

    i run dts package by using these following methods in store procedure ---

    sp_OACreate 'DTS.Package' --- create object

    sp_OAMethod --- loads the dts package

    sp_OASetProperty --- set package property

    sp_OAMethod --- Executes package

    sp_OADestroy --- Destroy package

    in sql server 2000

    BUT , now in sql server 2005, dts are replace by ssis packages..

    i have made a simple ssis package i wanted it to run in store procedure without using dtexec and xp_cmdshell commands .:exclamationmark:

    hope the post will be an interesting one ................

    thks in advances

  • October 5, 2007 at 5:17 am

    #739465

    Well, you could do it the same way you did the old one, but I wouldn't. Why don't you want to shell out to run it?

    My first response would be to add the package to the Job Agent and use msdb..sp_Start_Job. Tis, however, would have been my response for doing this in SQL 2000 as well (I'm not a big fan of xp_CmdShell either).

    If you do not like that option, you can create a CLR stored procedure to do this.

  • October 5, 2007 at 5:48 am

    #739477

    HI,

    Thanks 4 replay

    how to call ssis package in CLR store procedure

  • October 19, 2007 at 9:03 am

    #743666

    Can u Post the code sample , CLR program Calling SSIS

  • November 19, 2008 at 11:32 pm

    #900889

    Hi.. I am facing the same problem. I have a DTS which I can execute from SQL Server 2000 query analyzer with some parameters..

    useing the sp_OAcreate methods....

    But now in SSIS i could not do the same thing.... and I do not want to use dtexec.. and xp_cmdshell.. Because I have some input parameters which is used in the inner SSIS packages..

Viewing 5 posts - 1 through 4 (of 4 total)

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