Post reply

Best way to run scheduled SSIS Job by user input

  • May 20, 2011 at 1:07 pm

    #236765

    Hi,

    I'm looking for an option to run or activate a SSIS Job that is scheduled to run every night, so this job can be run by the user when he clicked a button inside a web page.

    Is it posible ?

    Thanks for your ideas.

  • May 22, 2011 at 11:49 pm

    #1328294

    Your question is a bit confusing: you want user input to start a scheduled job? That seems contradictory.

    Anyway, look into the stored procedure sp_start_job:

    http://msdn.microsoft.com/en-us/library/ms186757.aspx

    Be aware that this is an asynchronous stored procedure, e.g. if you run it, the Agent job is started and the stored procedure immediately returns the result (is the job started or not?). It doesn't wait for the result of the job.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 23, 2011 at 8:30 am

    #1328481

    Hi,

    Yes, we want to schedule a SSIS job project but also we want to allow a specific user to run that project whenever he wants...

    Thanks for your response, again....

    :w00t:

  • May 25, 2011 at 8:07 am

    #1329674

    Koen is on the right track. However, you will need to keep in mind that your application user will likely (and probably should) lack the rights to run the job. You might have to work a little "magic" to elevate the rights.

    CEWII

  • May 25, 2011 at 12:30 pm

    #1329891

    Elliott Whitlow (5/25/2011)

    You might have to work a little "magic" to elevate the rights.

    I've done this by creating a SQL Agent proxy for SSIS package execution and using EXECUTE AS in the stored procedure that executes sp_start_job.

    Greg

  • May 25, 2011 at 1:32 pm

    #1329913

    Greg Charles (5/25/2011)

    Elliott Whitlow (5/25/2011)

    You might have to work a little "magic" to elevate the rights.

    I've done this by creating a SQL Agent proxy for SSIS package execution and using EXECUTE AS in the stored procedure that executes sp_start_job.

    That indeed should do the trick. Nice solution.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 25, 2011 at 1:59 pm

    #1329929

    Agreed.

    CEWII

  • July 8, 2011 at 9:50 am

    #1350448

    Hi,

    🙂

    Resuming this task after a pause, I have encountered this situation, I hope you could help me

    when I run a job, finish with this message:

    Job's error. This job was invoked by Domain\Adminuser. Last step 1

    Executed as user: Domain\service account. Package Execution Error. Error in step

    So, for me there is a conflict between user who invoked the job and user who executed the job

    I have read that impersonation via 'EXECUTE AS' is not a secure method and must be used with precaution, +

    what other options do I have to save me of being every day locked with a chain waiting for files to be processed ?

    (read this http://www.sommarskog.se/grantperm.html)

    Is there a way to invoke and execute a job with the same user ?

    Remember, the goal task was:

    Do a data export from txt file to SQL Server 2005 table .

    I created a SSIS package for this task.if I run manually SSIS package, it run ok and data is exported ok.

    This task is needed daily

    I'm trying to schedule under jobs of SQL Server Agent this package but run with errors,

    I have selected :

    Type: SSIS Package

    Execute as: SQL Agent Account Service

    Origin:SSIS Package Storage

    Server:Testing

    Using Windows Authentication

    Package:\MSDB\SSISPACK01

    Thanks Again

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

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