SSDT Post Deployment Script - Run All Scripts In Folder

Question

I have a PostDeployment script in a SQL project which runs a number of other scripts in the project when published:

:r .\Scripts\Script1.sql 
:r .\Scripts\Script2.sql 
:r .\Scripts\Script3.sql 
:r .\Scripts\Script4.sql

Rather than having to manually update this each time a new script is added to the Scripts folder in source control (TFS) is it possible to just iterate and execute all the SQL scripts within the Scripts folder?

Answer 1

Add this to your project file

 <ItemGroup>
    <DataScripts Include="Data\*.sql" />
 </ItemGroup>
 <Target AfterTargets="BeforeBuild" Name="CreateDataScript">
    <Delete Files="DataScript.sql" />
    <WriteLinesToFile Overwrite="false" File="DataScript.sql" Lines=":r .\%(DataScripts.Identity)" />
 </Target>

The DataScripts property is the folder with all of the sql scripts you want to run. This generates the file before you do the build so you can reference it in the post deployment script without issue like so.

:r .\DataScript.sql

This can be used to dynamically generate scripts that can be referenced in pre / post deployment scripts