Failed to deploy project. For more information, query the operation_messages view for the operation identifier '4'

Question

I am using sql server 2012 and in it i have sql server data tools which is using visual studio 2010 so, i have one table which contains name only and i would like to copy this table in destination table so i have taken oledb source and oledb destination on data flow task and try to deploy that project into sql server "Integration service catalog" so a wizard is appearing asking for source and destination for deployment which i have provided and press ok at last i am getting this error.

- <WizardReport xmlns="http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
- <Results>
  <Children /> 
  <Header>Results</Header> 
- <ReportItems>
- <SummaryReportItem>
  <Message i:nil="true" /> 
  <Property>Loading project</Property> 
  <Status>Success</Status> 
  <Value i:nil="true" /> 
  </SummaryReportItem>
- <SummaryReportItem>
  <Message i:nil="true" /> 
  <Property>Connecting to destination server</Property> 
  <Status>Success</Status> 
  <Value i:nil="true" /> 
  </SummaryReportItem>
- <SummaryReportItem>
  <Message i:nil="true" /> 
  <Property>Changing protection level</Property> 
  <Status>Success</Status> 
  <Value i:nil="true" /> 
  </SummaryReportItem>
- <SummaryReportItem>
  <Message>Failed to deploy project. For more information, query the operation_messages view for the operation identifier '4'.</Message> 
  <Property>Deploying project</Property> 
  <Status>Failure</Status> 
  <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">System.Data.SqlClient.SqlException (0x80131904): Failed to deploy project. For more information, query the operation_messages view for the operation identifier '4'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.IntegrationServices.Common.ObjectModel.SqlHelper.ExecuteSQLCommand(SqlStoreConnection storeConnection, CommandType cmdType, String cmdText, SqlParameter[] parameters, ExecuteType execType, Int32 commandTimeout) at Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder.DeployProject(String projectName, Byte[] projectStream) at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeployExportProjectHelper.DeployProject(CatalogFolder folder, Project project) at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.Deploy(CatalogFolder folder, Project project) at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.DeployProject()</Value> 
  </SummaryReportItem>
  </ReportItems>
  </Results>
- <Summary>
- <ReportSection>
  <Children /> 
  <Header>Use the following arguments to perform this deployment from the command line:</Header> 
- <ReportItems>
- <SummaryReportItem>
  <Message /> 
  <Property>Command line</Property> 
  <Status>None</Status> 
  <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">/Silent /SourcePath:"C:\Users\AHMED\Documents\Visual Studio 2010\Projects\Integration Services Project3\Integration Services Project3\bin\Development\Integration Services Project3.ispac" /DestinationServer:"AHMED-PC" /DestinationPath:"/SSISDB/NewPack/Integration Services Project3"</Value> 
  </SummaryReportItem>
  </ReportItems>
  </ReportSection>
- <ReportSection>
  <Children /> 
  <Header>Source</Header> 
- <ReportItems>
- <SummaryReportItem>
  <Message /> 
  <Property>Path</Property> 
  <Status>None</Status> 
  <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">C:\Users\AHMED\Documents\Visual Studio 2010\Projects\Integration Services Project3\Integration Services Project3\bin\Development\Integration Services Project3.ispac</Value> 
  </SummaryReportItem>
  </ReportItems>
  </ReportSection>
- <ReportSection>
  <Children /> 
  <Header>Destination</Header> 
- <ReportItems>
- <SummaryReportItem>
  <Message /> 
  <Property>Server name</Property> 
  <Status>None</Status> 
  <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">AHMED-PC</Value> 
  </SummaryReportItem>
- <SummaryReportItem>
  <Message /> 
  <Property>Path</Property> 
  <Status>None</Status> 
  <Value i:type="a:string" xmlns:a="http://www.w3.org/2001/XMLSchema">/SSISDB/NewPack/Integration Services Project3</Value> 
  </SummaryReportItem>
  </ReportItems>
  </ReportSection>
  </Summary>
  </WizardReport>

Answer 1

This behaviour is seen when the operator account is not granted membership of the ssis_admin role, even if they are sysadmin.

In that case there are no matching records in operation_messages.

Answer 2

The message Failed to deploy project isn’t very useful, but the rest of the message is. The operation_messages view found in SSISDB, and the operation identifier number is how to determine what the error is. Run this query, using the number provided in the error message, which in this case is 4

You have to run the following query to read more details:

Select * from catalog.operation_messages where operation_id = 4

References

• http://www.desertislesql.com/wordpress1/?p=1140
• https://learn.microsoft.com/en-us/sql/integration-services/system-views/catalog-operation-messages-ssisdb-database