USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0



IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB NAME FROM TXT FILE', --JOB NAME
@enabled=1, --keep it 0, just deployed, as per requirement we can make it 1 so that alert mails can avoided
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'SOME DESCRIPTION IN TXT FILE', --DESCRIPTION
@category_name=N'[Uncategorized (Local)]', --CATEGORY
--Logins 4 environments

--can I make like below so that on 4 environments at a time this script will execute??

--Stage
@owner_login_name=N'CORP\StaSQLExec',
--DEV
@owner_login_name=N'CORP\DEVSQLExec'
--CT
@owner_login_name=N'CORP\CTESQLExec'
--RT
@owner_login_name=N'CORP\EXTSQLExec' @job_id = @jobId OUTPUT


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--Steps

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N'step name from txt file', --step name -step1 from txt file
--Step 1 --
@step_id=1, --if more than step 1 then repeate block
@cmdexec_success_code=0,
@on_success_action=1, -- make it 2
@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'/SQL "\DTS NAME FOR STEP 1" /SERVER npkstadat11 /CONFIGFILE "E:\SSIS_Package_Repository\DTS NAME FOR STEP 1\PackageDependencies_20120801\DTS.dtsconfig" /CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0

--Step 2 --
@step_id=2, --if step 3 then repeate block
@cmdexec_success_code=0,
@on_success_action=1, -- what is GO TO NEXT STEP code here??
@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'/SQL "\DTS NAME FOR STEP 2" /SERVER npkstadat11 /CONFIGFILE "E:\SSIS_Package_Repository\DTS NAME FOR STEP 1\PackageDependencies_20120801\DTS.dtsconfig" /CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0



IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'



IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'JOB NAME',
@enabled=0, --keep 1 so that alert mails can be avoided
@freq_type=8,
@freq_interval=127,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=1,
@freq_recurrence_factor=1,
@active_start_date=20080318, --start date
@active_end_date=99991231, -- end date
@active_start_time=91500,
@active_end_time=235959,
@schedule_uid=N'a4c6128a-c053-4041-bb07-53012cee1527'
-- what can we put here?
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'




IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION


GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO