none
Running SSIS from Stored Proc

    Question

  • Hello guys ,

    Iam totally new to SSIS , and i have been tasked with the follwoing.

    I have been asked to run an SSIS pacakge from a stored proc. I did manage to run an SSIS pacakge from the stored proc using xp_cmshell . The stored proc currently uses xp_cmdshell  to run DTEXEC  command and the returned parameter would indicate whether the SSIS ran successfully or not.

    The problem is , the SSIS pacakge takes about 40 min to complete.  

    Now comming  to think of it , is it a good idea to run a stroed proc to run SSIS as the stored proc will  stop running only when the SSIS stops/completes.  Wouldnt this be a strain on the SQL server to run a stored proc for such a long time ?


    Is there a way to over come this / would this be a problem to run it this way ?


    Hoping for some replies ..

    Ash
    Thursday, September 18, 2008 1:08 AM

Answers

  • You could write to a table by using an Execute SQL Task in the package, or a T-SQL step in the job. Or you could call the sp_help_job proc in a loop until it completes. If you do that, make sure to use a Sleep or WaitFor type of command, so that the process idles between checks.

    Thursday, September 18, 2008 3:31 PM

All replies

  • My preferred approach is to set up a SQL Agent job that calls the package, and launch that from the stored proc. You call sp_start_job to start the Agent job. sp_start_job returns as soon as the job kicks off, so it won't tie up your proc while the package runs.

     

    Thursday, September 18, 2008 1:22 AM
  • Thanks for the quick response. I know that i have the option of running a SQL agent job but I have also read in other fourms that all I can get is a confirmation as to whether the job started  or not ,and nothing related to the end of the job.

    I would need some kind of a confirmation which I can send back to the calling storedprocedure regarding the completion of the job. 

    How would I be able to acheive this.

    Thanks,
    Ash
    Thursday, September 18, 2008 11:02 AM
  • sp_help_job will return the job's status and last execution outcome.

     

    However, you seem to have conflicting requirements. You don't want the stored proc to wait 40 minutes on the package, but you want the stored proc to return the result of the package? You can force the proc to wait after launching the job, by calling WAITFOR in a loop, and using sp_help_job to see if the job has completed. But your stored proc will not return until the job completes, so your app will be waiting too. You could program the app to call the stored proc on a separate thread, or have the app itself poll the job (using sp_help_job).

    Thursday, September 18, 2008 1:31 PM
  • Sorry about the confusion. Ok, let me try to explain what Iam trying to do.

    I have an Aspx page, on which there would be a button to start off the SSIS pacakge. So in order to kick off the package I wrote a stored proc whcih did the follwoing



    DECLARE @PackagePath varchar(100), @returncode int

    SET @PackagePath = 'dtexec /F "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\' + @PackageName + '"'

    EXEC @returncode = xp_cmdshell @PackagePath

    SELECT @returncode


    After wrting this and exectuing the stroed proc on a dummy SSIS whcih ran quickly in about a min i realised that its would be great waste of resource if I did this with an SSIS pacakge which runs for almost 1 hr and I woulundt want the stored proc to wait for so long.

    Now the other option I havve as u say is running a job from the stored proc whcih runs asynchronously. and by that i wouldnt need the stored proc to wait on it.

    But I would want some kind of a confirmation from the job saying that it has completed the execution of the SSIS pacakge, as I have to send it back to the application.

    Is there a way i can achieve all this ? Or is there something I can do to make the job write into a table from whcih i can read off of later.


    Hoping for your reply.
    Thanks,
    Ash






    Thursday, September 18, 2008 2:53 PM
  • You could write to a table by using an Execute SQL Task in the package, or a T-SQL step in the job. Or you could call the sp_help_job proc in a loop until it completes. If you do that, make sure to use a Sleep or WaitFor type of command, so that the process idles between checks.

    Thursday, September 18, 2008 3:31 PM
  • Great thank you so much ... also i wanted to know if I could fire of emails at differnt steps of the job. Or may be fire off emails at diffrent steps of the pacakage execution.


    Thanks a Million again Smile
    Thursday, September 18, 2008 4:08 PM
  • You ought to be able to do both.  The SQL job can be set up to send emails, and/or you can also have tasks in the SSIS package (a "Send Mail" task) pop off an email...
    Thursday, September 18, 2008 6:15 PM