none
SQL Server Agent jobs in SQL Server 2012

    Question

  • We are in the process of converting from SQL Server 2005 to SQL Server 2012.  All our SSIS packages are now in SSISDB and we have a SQL Agent job to run the packages from the catalog.  Our problem is that when we scripted out the job, we found the server name was hard coded into the @command.  I have tried putting in environment variables, but that does not seem to address the server name. Is there any way to put a variable in there for the server name to make it deploy-able on our production server? 

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'File_Delete.dtsx', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=3, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'SSIS', 
    @command=N'/ISSERVER "\"\SSISDB\File\File Packages\File_Delete.dtsx\"" /SERVER DWDEV03 /Par "\"$Project::DW\"";"\"Data Source=DWDEV03;Initial Catalog=DW;Provider=SQLNCLI11;Integrated Security=SSPI;\"" /Par "\"$Project::Import_Dir\"";"\"\\dwdev03\DW\ImportDir\\"" /Par "\"$Project::Stage\"";"\"Data Source=DWDEV03;Initial Catalog=Stage;Provider=SQLNCLI11;Integrated Security=SSPI;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', 
    @flags=0



    • Edited by 2012newbie Wednesday, September 26, 2012 6:33 PM
    Wednesday, September 26, 2012 6:32 PM

Answers

All replies

  • There are SQL Agent Tokens that may help you.  Here is the link: http://msdn.microsoft.com/en-us/library/ms175575.aspx

    Particularly notice the (SRVR) token.  Be sure to read about and use the Escape Tokens.

    RLF

    Wednesday, September 26, 2012 9:49 PM
  • Thank you!  It worked!

    Replaced the server name with  $(ESCAPE_NONE(SRVR))

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'File_Delete.dtsx', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=3, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'SSIS', 
    @command=N'/ISSERVER "\"\SSISDB\File\File Packages\File_Delete.dtsx\"" /SERVER $(ESCAPE_NONE(SRVR)) /Par "\"$Project::DW\"";"\"Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=DW;Provider=SQLNCLI11;Integrated Security=SSPI;\"" /Par "\"$Project::Import_Dir\"";"\"\\$(ESCAPE_NONE(SRVR))\DW\ImportDir\\"" /Par "\"$Project::Stage\"";"\"Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=Stage;Provider=SQLNCLI11;Integrated Security=SSPI;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', 
    @flags=0

    Thursday, September 27, 2012 1:09 PM