Search code examples
sql-serverstored-proceduresparametersssisautosys

How to run a stored procedure without changing the as of date parameters using autosys

Execute METADATA.Runprocess
    @p_PROCESS_NAME = 'WEEKLY LOAD'
    ,@p_AS_OF_DATE = = '9/20/2016'
    ,@p_SOURCE_SYSTEM = '2'
    ,@p_ETL_ORDER = 'ETL'
    ,@p_TYPE_LOAD = '1'
    ,@p_TIME_FRAME = 'W'
    ,@p_IS_AUTOMATED = 0`

The above stored procedure needs to be run every week on a Tuesday. Currently I am running this stored procedure manually having to change the @p_AS_OF_DATE every week. I would love to automate this using autosys. Where would would be the best place to set this up?

Solution

  • Create a new job via SQL Agent and schedule it to run every Tue.

    As for @p_AS_OF_DATE, inside TSQL task of that new job, you can declare a new date variable and set it to GETDATE(). Then you can pass the variable to SP call.