Background
Lately, I have been working with Azure Data Factory for a customer who has large number of SSIS packages already well written, functioning, and deployed on their on-premise live infrastructure; however, now, everything needs to be ported to Azure for obvious reasons of costing and minimizing the dependency on their on-premise assets and making their way all the way through Capex to Opex, and hence Azure Data Factory is the best fit for it.
Now, the quicker approach to get all these existing SSIS packages running up there in the cloud is by making use of the SSIS runtime which enables you to run your existing packages in Azure and it is as easy as any lift and shift approach of deployment.
Long story short
Whenever you make any change in your SSIS package, the change needs to be published / deployed in your SSIS DB so that your next package run can pick it up. Visual Studio or SSMS has in-built features to make sure that these changes are deployed smoothly in few clicks by making use of the deployment wizard but wait .. there is always a but in everything which sounds too easy, isn't there?
Many times, whenever you try and deploy your packages using this deployment wizard; e.g., either by using Visual Studio or SSMS - you might have encountered an infamous error message something like this, click on the failed option for details and you see mysterious message which says nothing but
"Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)"
This leaves you in a confused state and you spend hours wondering -- where to look for an answer?
Well, there is always a friend in need and his name is Google / Bing. The developer community across the globe is generous enough to post their experiences troubleshooting this error and for some of you it could help and you are all set, however it did not help me in this instance!
Since this error seems to be quite common and workarounds mentioned in existing posts could help you out so make sure you also check out these fantastic links
Well, what worked for me then?
Two things - When nothing worked from all the suggestions mentioned on various blog posts - I went ahead and deleted the existing deployed project and tried re-deploying using the same VS deployment wizard and voila - error went away and I could manage to deploy fine. But hold on - don't get excited yet, there is an incoming but here.. but.. the same error message started surfacing for upcoming deployments.
Again I thought of deleting the existing deployment on SSISDB and tried to re-deploy - but it did not work again.
From a few links, I learned about how the stored procedure actually prepares your deployment before it is deployed, and so I thought of taking a look at it. The name of this stored procedure is "[internal].[prepare_deploy]", you can find this stored procedure in your SSISDB.
Open this stored procedure and search for the error numbers shown in detailed error message; i.e.27118, and it points to one of the lines from where the exception is being thrown with this error number.
The logic there is that, it searches for the project_id in one of the internal tables named as "[internal].[projects]" and if finds it - then it throws an error.
So, it's easy to guess what I did - went to the "[internal].[projects]" table in SSISDB, tried querying all the content of it by selecting * from [internal].[projects], I could see that there is one entry sitting in this table which looked like it was for the project I was trying to deploy - guess what, I simply deleted the entry from the table and tried re-deploying the project using the same VS deployment wizard and again it started working.
I hope this helps someone who is still facing the issue and clueless after trying out all the suggestions provided on other posts.