locked
pass parameter to job RRS feed

  • Question

  • my ssis package is called via a job.
    i.e.: EXEC msdb..sp_start_job @job_name = @JobName 
    Is it possible to add an extra parameter here, i.e. pathname such as:
     EXEC msdb..sp_start_job @job_name = @JobName, @PathName

     where @pathname is used inside the ssis package?
    Thanks

    Monday, December 12, 2011 3:48 PM

Answers

  • This blog post must be helpful to you: http://sqlblog.de/blog/2009/09/ssis-execute-package-via-stored-procedure/

    However, please see what are the only parameters the sp_start_job accepts:

    [ @job_name= ] 'job_name'

    The name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.

    [ @job_id= ] job_id

    The identification number of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_id is uniqueidentifier, with a default of NULL.

    [ @error_flag= ] error_flag

    Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

    [ @server_name= ] 'server_name'

    The target server on which to start the job. server_name is nvarchar(128), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.

    [ @step_name= ] 'step_name'

    The name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL

    [ @output_flag= ] output_flag

    Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.


    Arthur My Blog
    • Marked as answer by arkiboys Monday, December 12, 2011 5:13 PM
    Monday, December 12, 2011 4:50 PM

All replies

  • There is nothing else you can add to the sp_start_job itself, but it is possible to manipulate on paths via a config file at the package level.

     


    Arthur My Blog
    Monday, December 12, 2011 4:21 PM
  • Not sure what you mean...
    Can you please give me an example on executing the job i.e. my sql above and pass a parameter which the ssis package uses?
    Thanks

    Monday, December 12, 2011 4:40 PM
  • This blog post must be helpful to you: http://sqlblog.de/blog/2009/09/ssis-execute-package-via-stored-procedure/

    However, please see what are the only parameters the sp_start_job accepts:

    [ @job_name= ] 'job_name'

    The name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.

    [ @job_id= ] job_id

    The identification number of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_id is uniqueidentifier, with a default of NULL.

    [ @error_flag= ] error_flag

    Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

    [ @server_name= ] 'server_name'

    The target server on which to start the job. server_name is nvarchar(128), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.

    [ @step_name= ] 'step_name'

    The name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL

    [ @output_flag= ] output_flag

    Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.


    Arthur My Blog
    • Marked as answer by arkiboys Monday, December 12, 2011 5:13 PM
    Monday, December 12, 2011 4:50 PM
  • Thank you
    Monday, December 12, 2011 5:13 PM