SSIS 2012 Deployment Errors

Thanks To Original Thread:

http://capstonebi.blogspot.de/2012/09/ssis-2012-deployment-frustrations.html

https://connect.microsoft.com/SQLServer/feedback/details/804901/ssis-2012-deploying-new-versions-of-large-projects-runs-into-a-timeout-during-deployment-into-ssis-catalog

Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203)

Solution:
To verify detail error message:
======================
select * from catalog.operation_messages
where operation_id=10069

The message column reads as follows:

 

Failed to deploy the project. Fix the problems and try again later.:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

As the person deploying the project, I can’t “help” the Deployment Wizard with tweaking any queries, but I could see what it was doing using SQL Profiler.  The query that caught my attention is the following:

 

exec [internal].[sync_parameter_versions] @project_id=2,@object_version_lsn=10

Notice the number of reads(1.7M) and the duration(30 seconds).  I acquired the query plan for this stored procedure and found that it would run much faster if the following two indexes are applied to the SSISDB database.

USE [SSISDB]

GO

CREATE NONCLUSTERED INDEX [ix1_internal_object_parameters_inc] ON [internal].[object_parameters]

(

        [project_id] ASC ,

        [project_version_lsn] ASC

)

INCLUDE (      [parameter_id],

        [object_type],

        [object_name],

        [parameter_name],

        [parameter_data_type],

        [required],

        [sensitive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [ix2_internal_object_parameters_inc] ON [internal].[object_parameters]

(

        [project_id] ASC ,

        [project_version_lsn] ASC ,

        [object_type] ASC ,

        [object_name] ASC ,

        [parameter_data_type] ASC ,

        [required] ASC ,

        [sensitive] ASC

)

INCLUDE (      [parameter_name],

        [default_value],

        [sensitive_default_value],

        [value_type],

        [value_set],

        [referenced_variable_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Error :
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65554. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Solution 1: KB Article
https://support.microsoft.com/en-us/kb/918040

Solution 2: Change the Permission Set : External

While doing this faced the below error :

ERROR: 
The database owner SID recorded in the master database differs from the database owner SID recorded in database
To check who is the owner listed in the master database run the following:

SELECT  SD.[SID]
       ,SL.Name as [LoginName]
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = ''

To correct this issue, run the following command:

Alter Authorization on Database:: to []

Once done set the required settings on the properties of Assembly.