1

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;
2
  • Does this answer help ?
    – Kin Shah
    May 3 '16 at 23:37
  • @Kin thanks for the link! I was looking for a more automated solution that could be used in a CI environment. May 4 '16 at 0:07
3

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.

1
  • Worked like a charm, had to add an extra view but helped all the deployments.
    – snp.it
    May 21 '19 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.