Sql-server – How to set ENVREFERENCE when creating a SQL Server job

jobssql serversql server 2014

SQL Server 2014 (if it matters)

When I am migrating SSIS packages and jobs between environments, I need to update the /ENVREFERENCE parameter to the command for each job step. Is there anyway to find the correct value and script the job step to use this value.

Pseudocode

Query for ENVREFERENCE where Environment name = N'QA' -- how to do this?
EXEC sp_update_jobstep 
    @job_name = N'My Job', 
    @step_id = 30, 
    @command= first part + /ENVREFERENCE= value from query + last part;

Best Answer

I've used code like the following in my job scripts to associate the step with the proper environment:

--Call SSIS package. Figure out the server and Environment Reference variables
DECLARE @EnvReference   INT = (SELECT TOP 1 EnvironmentReferenceID FROM SSISDB.rpt.Package WHERE PackagePathName = '\SSISDB\Folder\Project\Package.dtsx\');
DECLARE @Command        VARCHAR(2000) = FORMATMESSAGE(N'/ISSERVER "\"\SSISDB\Folder\Project\Package.dtsx\"" /SERVER %s /ENVREFERENCE %i /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', @@SERVERNAME, @EnvReference);
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Name', 
                @step_id=1, 
                @on_success_action=3, 
                @on_fail_action=3, 
                @subsystem=N'SSIS', 
                @command=@Command, 
                @database_name=N'master', 
                @output_file_name=N'E:\Log\Job.log',
                @flags=40;

By the way the view rpt.Package is just this:

SELECT  PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F2.name, PJ.name, PK.name),
    EnvironnmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F.name, E.name),
    EnvironmentReferenceID = ER.reference_id,
    ProjectFolder = F.name,
    Project = PJ.name,
    Package = PK.name,
    EnvironmentFolder = F2.name,
    Environment = E.name
FROM    catalog.folders AS F
    INNER JOIN catalog.environments AS E ON E.folder_id = F.folder_id
    INNER JOIN catalog.environment_references AS ER ON (ER.reference_type = 'A'
            AND ER.environment_folder_name = F.name
            AND ER.environment_name = E.name)
        OR (ER.reference_type = 'R'
            AND ER.environment_name = E.name)
    INNER JOIN catalog.projects AS PJ ON PJ.project_id = ER.project_id AND PJ.folder_id = F.folder_id
    INNER JOIN catalog.packages AS PK ON PK.project_id = PJ.project_id
    INNER JOIN catalog.folders AS F2 ON F2.folder_id = PJ.folder_id

Make sure to replace the references: Folder, Project, Package, Job with your own strings.

Related Question