'Failed to deploy project. For more information, query the operation_messages view for the operation identifier '20132'?
The Packages was deployed fine Initially , i have made few changes and try to deploy it again. i am receiving the following Error:
Failed to deploy project. For more information, query the operation_messages view for the operation identifier '20132'
When I query the SSIS DB with select * from catalog.operations_messages no rows are returned, just column headers are showing in the Data. Please help
Solution 1:[1]
First, Refering to this Microsoft Link, This view requires one of the following permissions:
- READ permission on the operation
- Membership to the ssis_admin database role
- Membership to the sysadmin server role
Check that you have the right permissions to access this view
Second, The SSISDB.catalog.operation_messages table stores the package execution log. If there is no package executed on this server, the SSISDB.catalog.operation_messages table will be empty.
And the package deployment log is stored in [internal].[packages] table and [internal].[projects] table
You can use a similar query to read info from these tables:
select * from internal.packages a inner join internal.projects b on a.project_id = b.project_id
Solution 2:[2]
have a look at your SQL Server Logs
Yesterday I had a similar issue without messages in catalog.operation_messages table.
I had only the row in catalog.operations table, referred to the deploy.
Today googling the web I found that it might be a memory issue.
In the log mentioned before, I got those messages (see below) at the exact time when I deployed my integration... and WOW! I realized that my VM box has 2GB (total amount of RAM), 1GB maximum mem for ms sql server... :(
This is a machine that we are using only for SSIS management and test purpose, ...anyway do you think that 2GB for the machine and 1GB for the server are enough?
Today I tried again the deploy and luckily (or randomly) it worked, with the same amount of ram and with same messages (fewer than yesterday for the same deploy).
01/17/2018 15:25:10,spid22s,Unknown,AppDomain 5 (SSISDB.dbo[runtime].4) unloaded.
01/17/2018 15:25:09,spid14s,Unknown,AppDomain 5 (SSISDB.dbo[runtime].4) is marked for unload due to memory pressure.
01/17/2018 15:24:54,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 5 (SSISDB.dbo[runtime].4).
01/17/2018 15:24:54,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 5 (SSISDB.dbo[runtime].4).
01/17/2018 15:24:54,spid69,Unknown,AppDomain 5 (SSISDB.dbo[runtime].4) created.
01/16/2018 17:25:07,spid27s,Unknown,AppDomain 4 (SSISDB.dbo[runtime].3) unloaded.
01/16/2018 17:25:06,spid27s,Unknown,AppDomain 4 (SSISDB.dbo[runtime].3) is marked for unload due to memory pressure.
01/16/2018 17:24:03,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 4 (SSISDB.dbo[runtime].3).
01/16/2018 17:24:03,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 4 (SSISDB.dbo[runtime].3).
01/16/2018 17:24:03,spid69,Unknown,AppDomain 4 (SSISDB.dbo[runtime].3) created.
01/16/2018 17:24:02,spid15s,Unknown,AppDomain 3 (SSISDB.dbo[runtime].2) unloaded.
01/16/2018 17:24:02,spid15s,Unknown,AppDomain 3 (SSISDB.dbo[runtime].2) is marked for unload due to memory pressure.
01/16/2018 17:23:57,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 3 (SSISDB.dbo[runtime].2).
01/16/2018 17:23:57,spid69,Unknown,Unsafe assembly 'microsoft.sqlserver.integrationservices.server<c/> version=11.0.0.0<c/> culture=neutral<c/> publickeytoken=89845dcd8080cc91<c/> processorarchitecture=msil' loaded into appdomain 3 (SSISDB.dbo[runtime].2).
01/16/2018 17:23:57,spid69,Unknown,AppDomain 3 (SSISDB.dbo[runtime].2) created.
Solution 3:[3]
Now this message is quite useful as it provides information that I can use to fix the issue. This SSIS Project contains a date parameter BackDate, which I had not set. Here’s the parameter.
SSISVSParameters
I set this parameter to date between 1/1/1753 and 12/31/9999 and deployed the project again. This time, no error.
I hope that you have found this post helpful, especially if you haven’t deployed a package to SSIS in SQL Server 2012 or later.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Hadi |
| Solution 2 | leonardo4it |
| Solution 3 | Shivenndoo |