Post reply

User initated start of SSIS package

  • November 10, 2009 at 10:11 am

    #156394

    Hi there,

    What is the best approach to be able to let users start an SSIS package on demand? We haev a situation that requires that approach. It does not have to be fancy it can even be an SSIS package started up from a bat file or a link in a web site or whatever you suggest.:w00t:

    Thank you

  • November 10, 2009 at 10:38 am

    #1077389

    There are other options (http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx), but I've found the best way to do this is to create an unscheduled SQL Agent job to run the package and use sp_start_job to start it. I had to create a security credential and a SQL Agent proxy.

    Greg

  • November 10, 2009 at 11:07 am

    #1077394

    thanks Gregg! You have directed me in the right path.

  • November 10, 2009 at 1:28 pm

    #1077481

    I've got a category #6 from the link (invent your own). I have 4 packages that I envoke via Serivce Broker.

    How, you might say? Well, the first task in the Control Flow for the package is an ExecuteSQL task that runs a stored procedure. That stored procedure polls a Serivce Broker queue. So I start the package up each day and it runs all day. The only time it actually 'does' anything though is when I send a message into the SB queue. The message triggers the package execution and the package runs per the instructions inside the XML message. Pretty slick. The final task in the package sends a reply message and the user gets the reply XML in SSMS in the results pane from running the stored procedure.

    I wanted to have a way to integrate multiple packages into one master package but also have the ability to envoke the packages independantly of each other so I built out a message based SSIS platform. If I run my master package, it envokes the child packages the same way, by creating XML messages and queing them via Service Broker. This gives me the flexibility to run the package contents via stored procedure calls.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 10, 2009 at 2:03 pm

    #1077509

    wow, pretty nice stuff there!

    Thanks

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

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