SSIS 2012 Environment Reference Not Associated with Project

ssis-2012

I have a package that I am updating. The package is executed by a Job Agent job. After deleting the package and then deploying the new version, I run a script that executes any necessary [create_environment], [create_environment_reference], [create_environment_variable], and [set_object_parameter_value] statements.

However, when I start the job, it fails with the following error message (where "n" is a number which doesn't appear in any of the relevant SSISDB tables or views as an id of any kind):

The environment reference n is not associated with the project

Best Answer

The reason for this is that the existing job step that executes the package contains a /ENVREFERENCE switch in the command line, and that value must be updated for the new version of the package.

This is not obvious, as using SSMS to view the job step doesn't display this switch anywhere. The only way that I have found to fix this is to script out a drop and create of the job, obtain the correct reference_id using code like this:

SELECT  reference_id
  FROM  SSISDB.[catalog].environment_references er
        JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
 WHERE  er.environment_name = @Environment
   AND  p.name              = @ProjectName;

and then updating the @command parameter of the call to sp_add_jobstep with the new number.

Helpful references (that you won't find by searching for the error message):

Related Question