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)
Best Answer
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:
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: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: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:Obviously, to see some result from this view user should have the permission either
ssis_admin or sysadmin
. Let's see who executes procedureinternal.prepare_deploy
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 userAllSchemaOwner
to rolessis_admin
and problem is being gone.