Hey there, been a while that I have updated this blog but maybe this one notorious error inspired me to write one post and so here I am, sharing a short but an useful tip which could save someone's hours of efforts.
Background - Lately, have been working with Azure data factory for one of the 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 make their way all the way through Capex to Opex and hence Azure data factory is the best fit for it. Now, quick 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 and always a but in everything which sounds too easy, isn't it?
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 error seems to quite common and workarounds mentioned in existing posts could help you out so make sure you also check out these fantastic links
Background - Lately, have been working with Azure data factory for one of the 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 make their way all the way through Capex to Opex and hence Azure data factory is the best fit for it. Now, quick 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 and always a but in everything which sounds too easy, isn't it?
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 error seems to 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 same VS deployment wizard and voila - error went away and could manage to deploy fine, hold on - don't get excited yet, there is an incoming but here.. but.. the same error message started surfacing for upcoming deployments.
Again thought of deleting the existing deployment on SSISDB and tried to re-deploy - but it did not work again.
Again thought of deleting the existing deployment on SSISDB and tried to re-deploy - but it did not work again.
From few links, could know about the stored procedure actually prepares your deployment before it is deployed, and so 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 number shown in detail error message i.e.27118 and it points to one of the line from where exception is being thrown with this error number.
The logic there is that, it searches for the project_id in one of the internal table named as "[internal].[projects]" and if finds it - then it throws an error.
So, easy to guess what I did - went to the "[internal].[projects]" table in SSISDB, tried querying all the content of it by select * from [internal].[projects], could see that there is one entry sitting in this table which looked like 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 same VS deployment wizard and again it started working.
Hope this helps someone who is still facing the issue and clueless after trying out all the suggestions provided on other posts.
