3

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)

3
  • Interesting. Have you ever been able to deploy an ispac to the server in question? This thread discusses that there might be network latency at play – billinkc Jul 2 '15 at 14:29
  • Thanks for comment @billinkc. Yes, I deployed .ispac on this server many times before. Do you mean that the main problem it's permission on file system? – Aleksandr Fedorenko Jul 3 '15 at 6:39
  • @billinkc Could you please take a look at my answer? I think it will be interesting for you. – Aleksandr Fedorenko Jul 20 '15 at 7:00
5

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:

enter image description here

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.

3
  • Awesome, glad you found the issue and what a weird error to have raised by the deploy procedures. Any idea how the AllSchemaOwner user lost the ssis_admin role? – billinkc Jul 20 '15 at 15:43
  • There is one answer that the permission was restricted by dba. And I don't know why)) – Aleksandr Fedorenko Jul 21 '15 at 7:22
  • I had the same issue, but my AllSchemaOwner hadn't lost any privileges. I deleted the old project and then deployed a fresh version, and that fixed it too. weird. – user79818 Nov 4 '15 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.