Multiple post-deployment scripts with SQL server project
SQL Server Database project has various features and functionality to work with database within your .NET solution. We can maintain the DB objects via scripts, source control them and publish them with different environments (dev/test/prod) manual publishing or via the CI / CD.
I started with single post-deploy script (…_subscriptionData.sql) and published it on the server and it all worked as expected.
Maintaining your post-deployments in multiple files helps you in segregating them based on feature deployment. This will save you from having conflict resolution when working on a larger project. When I tried adding another script (…_languageData.sql) under the post-deploy folder and tried pushing it, only the first scripts content was executed.
The script in the second file was not shown in the the generated script for deployment, as a result it was not published to the DB.
A project can have only one pre-deployment and one post-deployment script.
According to the documentat, post deployment should be single script hence the rest if the script files will not be executed. However we can extend this feature to have multiple sql files to be executed by adding SQLCMD syntax and variables in main scripts and set these in the database project properties.
Create the first post-deployment script with a generic name such as Script.PostDeployment.sql and in that file mention all your different post-deployment scripts with :r .\myfile.sql.
This extends the feature of having multiple script files as post-deployment scripts. Now, when SQL project is built and published it will have all the script file’s content in the post-deployment script.
Do note that, the file you have added as main post deployment script should have its Build Action set to PostDeploy.