SSIS: SSIS Package failure – Environment reference Id: NULL. Description: The parameter ‘ReportDate’ does not exist

Error message:

Executed as user: red\mm.ahAgent. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 17:10:59 Failed to execute IS server package because of error 0x80131904. Server:server01, Package path: \SSISDB\TTT\packageReports\02c package.dtsx, Environment reference Id: NULL. Description: The parameter ‘ReportDate’ does not exist or you do not have sufficient permissions. Source: .Net SqlClient Data Provider Started: 17:10:59 Finished: 17:10:59 Elapsed: 0.296 seconds. The package execution failed. The step failed.

 

Solution:

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

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..

 

Advertisements