SSIS 2012: The environment reference n is not associated with the project

Question

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

Answer 1

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):

• http://www.biadmin.com/2012/08/ssis-2012-gotcha-dtexecexe-and.html
• http://blogs.msdn.com/b/mattm/archive/2011/07/24/using-dtexec-with-packages-on-the-is-server.aspx

Answer 2

Old post but to help others and might be a solution: When the SSIS Package is executed from a job it's going to use the references set on the SSIS Project catalog.

When deploying a SSIS project again this references are lost and you need to reset them in the SSIS Catalog.

In SSMS > Connect the instance > open tree Integration Services Catalogs > SSIDB > Projects > Right click the project containing the Package executed in the SQL Agent Job > Click Configure > Click References

Is the reference still there? If not add it again. You can also script this as a post deployment sqlscript..

Answer 3

You can script out creating the reference, the environment variables and the association to the parameter using:

1. catalog.create_environment_reference (to link the project to the environment)
2. catalog.create_environment_variable (to create various values in your environment)
3. catalog.set_object_parameter_value (to assign the environment variable to the parameter)

If you do this prior to creating the agent job you can use the environment reference ID in your agent job creation command text.