SSIS 2012 Deployment Error “The project or operation records do not exist”
Although the new SSIS 2012 Project Deployment Model is pretty cool, it is a completely new deployment architecture that is bound exhibit some bugs. To be honest SSIS 2012 in general seems to be less geared up towards enterprise level scale-out, and will work better in a non-24/7 load environment.
The error I am experiencing with SSIS 2012 is to do with deploying a project (from SQL Server Data Tools) to an SSIS Catalog DB, whenever a project is deployed, there seems to be a 50/50 chance that I will receive the following deployment error:
Failed to deploy project. For more information, query the operation_messages view for the operation identifier ‘123456’. (Microsoft SQL Server, Error: 27203)
Upon checking the [catalog].[operation_messages] view, the error message will seem vague and will look something like this:
The project or operation records do not exist or you have not been granted the appropriate permissions to view them.
Even though I am certain there is no security issue as I administer the SSIS instance myself, and so the error is actually a bit (completely?) miss-leading.
After further diagnosing the issue through SQL Server Profiler, I reached to the conclusion that this is neither deadlocks or any kind of locking on the SSIS Catalog DB, in fact no such event is raised during any of the test scenarios I ran investigating the problem. What seems to be happen is that once a project is deployed onto an SSIS instance, there seems to be a procedure that kicks that checks if the project deployment process is currently running (basically has a status of “Running”), using the following procedure:
Were the @operation_id parameter is replaced with whatever local operation_id is associated with SSIS 2012 project deployment call, if this value does not exist (i.e. table returns no records) SSIS will basically escalate a failure on the deployment, giving the descriptive error message above.
What seems to be happening is that there is a delay between a SQL Server Data Tools logging a deployment operation within the SSIS Catalog DB, and the procedures on the Catalog DB side that kicks-off whenever a deployment started, and this disconnect is causing the deployment procedures on the SSIS Catalog DB to kick-off before a project started deployment, which eventually causes the error above.
Why is the operation not being logged before the Catalog DB starts its own procedure am not entirely sure, there could be many reasons for that behaviour, such as time-out value not set up correct or something is deadlocking with the deployment process and not allowing it to log the operation, I cant be sure. I have a ticket open on MSDN Social in order to resolve this issue but so far no joy.
How to Resolve “The project or operation records do not exist” Error
Well the solution is easy, albeit abit stupid, but it works!. Essentially all you need to do is just keep trying to deploy the project repeatedly, like I said there is 50/50 percent chance it will work without throwing the issue above.
Until I manage to figure-out a long term solution for this, brute force repeated deployment of the new SSIS Project Deployment Model seems to do the job.
I’ve come across this before and also found it’s a 50/50 chance of success. Great explanation of the cause and has helped my understanding of the cogs behind the scenes. Thx