I have a couple of SSIS packages to deploy from QA to Production.
Deploy the SSIS packages and ran the SQL Server Agent jobs creations, as well.
Ran into errors and one of them is referenced below.
Date 8/9/2017 3:37:21 PM
Log Job History (DataRefresh_pp_dem_PROD_to_DEV)
Step ID 1
Job Name DataRefresh_ppsivr_dem_PROD_to_DEV
Step Name DataRefresh_dem_PROD_to_DEV
Executed as user: LAB-DOM\svcProxy. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:37:22 PM Failed to execute IS server package because of error 0x80131904. Server: p-db-red, Package path: \SSISDB\SSISDB\DataRefrehPackage_Ppsivr_Dem_PROD_To_DEV\DataRefresh_Dem.dtsx, Environment reference Id: NULL. Description: Login failed for user ‘LAB-DOM\svcProxy’. Source: .Net SqlClient Data Provider Started: 3:37:22 PM Finished: 3:37:22 PM Elapsed: 0.562 seconds. The package execution failed. The step failed.
For us the problem was that the script we used for creating the SQL Server Agent job was hardcoded to look for the SSIS package in the originating SQL Instance’s SSISDB database.
The fix is to change the Server’s text box text to have the deployed SQL Instance name or simply the generic SQL Server Instance Identifier (local); especially if it is the default SQL Instance.