Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)

I try to deploy new version of existing SSIS project into Intergration Service

Catalog and getting error:


"Failed to deploy the project. Try again later. (Microsoft SQL Server,
Error: 27118)"

New .ispac file I have on file system (drive "D:\").
I tried:

• deploy from SSMS

• from command line

• with Power Shall script

And I got the same error.

(SQL Server 2012)

Problem has been fixed. So I'll try to decribe solution to this problem and maybe for someone it'll be useful and save time. In the beginning we have an error message from wizard:


"Failed to deploy the project. Try again later. (Microsoft SQL Server,
Error: 27118)"

Let's see detailed error, to do this just need to click on "Show technical details button". In the section "Additional data" we can find object wich is responsible for this error. In my case it was procedure prepare_deploy. This is an internal store procedure of database SSISDB. To catch an error I used statement RAISERROR in deferent places, because this error appears in different places. Thus my problem was there:

IF EXISTS (SELECT [project_id] FROM [internal].[projects]
            WHERE [folder_id] = @folder_id AND [name] = @project_name)
    BEGIN
        RAISERROR(27118, 16, 1) WITH NOWAIT
        RETURN 1                
    END

But to see this error you should have true in the following condition: IF(@project_id IS NULL) See whole statement which appears earlier in the store procedure:

SET @project_id = (SELECT [project_id] FROM [catalog].[projects]
                   WHERE [folder_id] = @folder_id AND [name] = @project_name)

    IF(@project_id IS NULL)

    BEGIN
    ...

So we have almost identical SELECT statements except schema, but results are different: first statement gives us nothing although second gives some result.

The schema [catalog] says that we have a deal with view. Take a look on the view:

CREATE VIEW [catalog].[projects]
AS
SELECT     ...

FROM       [internal].[object_versions] ver INNER JOIN
           [internal].[projects] proj ON (ver.[object_id] = proj.[project_id]
           AND ver.[object_version_lsn] = proj.[object_version_lsn]) INNER JOIN
           [internal].[folders] ON proj.[folder_id] = [internal].[folders].[folder_id]
WHERE      (ver.[object_status] = 'C') 
           AND (ver.[object_type]= 20) 
           AND (
                  proj.[project_id] IN (SELECT [id] FROM [internal].[current_user_readable_projects])
                  OR (IS_MEMBER('ssis_admin') = 1)
                  OR (IS_SRVROLEMEMBER('sysadmin') = 1)
                )

Obviously, to see some result from this view user should have the permission either ssis_admin or sysadmin. Let's see who executes procedure internal.prepare_deploy

ALTER PROCEDURE [internal].[prepare_deploy]
    ...
WITH EXECUTE AS 'AllSchemaOwner'
AS

Finally, checking permission for user AllSchemaOwner in database SSISDB:



Bingo, we don't have appropriate permissions to see data from view [catalog].[projects]. Just adding user AllSchemaOwner to role ssis_admin and problem is being gone.

StackExchange Database Administrators Q#105770, answer score: 6