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?

Foreyard

I actually managed to get this working in my Project, including all sql-scripts in a specified folder automatically on each build, no need to include them manually in post-script file, it's being done automatically now. Using msbuild commands in an included proj-file! – Hypogeous

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

Lonnie

Answer 2

If you need to keep manipulating this then you are likely using SSDT wrong. SSDT is a schema based versioning system not a script based system.

If you want a traditional script system then you should look to Ready Roll or SSW SQL Deploy.

Husbandry