I have found out how this could be done.
First you need to enable xp_cmdshell utility
RAISERROR ('Enabling xp_cmdshell utility...', 0, 1) WITH NOWAIT
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
Next you have to define a stored procedure which will do all the work. It works by reading all files into a temporary table and then runs SQLCMD command to parse each of found *.sql files
CREATE PROCEDURE [Builder].[RunScriptsInFolder]
@scriptsDir varchar(255)
AS
IF len(@scriptsDir) = 0
RETURN 0
DECLARE @Message VARCHAR(254)
SET @Message = 'Loading files in ' + @scriptsDir + ' directory...'
RAISERROR (@Message, 0, 1) WITH NOWAIT
DECLARE @FileList Table (FileNumber int identity(1,1), FileName varchar(255), Command varchar(2048))
DECLARE @OutputTable Table (Output varchar(MAX))
DECLARE @FileName varchar(255)
DECLARE @Command varchar(2048)
DECLARE @FileNum int
DECLARE @databaseName varchar(255)
SET @databaseName = db_name()
SET @Command = 'DIR /B /O:-N ' + @scriptsDir + '*.sql'
INSERT INTO @FileList (FileName) EXEC xp_cmdshell @Command
UPDATE @FileList SET Command = 'sqlcmd -d ' + @databaseName + ' -i "' + @scriptsDir + FileName + '"'
WHILE EXISTS(SELECT * FROM @FileList)
BEGIN
SELECT TOP(1) @FileNum = FileNumber, @FileName = FileName, @Command = Command FROM @FileList
SET @FileName = ' :r ' + @FileName
RAISERROR (@FileName, 0, 1) WITH NOWAIT
EXEC xp_cmdshell @Command
DELETE FROM @FileList WHERE FileNumber = @FileNum
END
RETURN 0;
All you have to do now is call this stored procedure (You need to pass full path to folder containing SQL files. You can get path to your project from MSBuild properties). Also note that I've added few lines around the call to RunScriptsInFolder call. Because you won't know in which order files in your folder are executed you should disable all foreign keys checks before doing it and enable them once you finished
RAISERROR ('Disabling all constraints...', 0, 1) WITH NOWAIT
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
---- Run all files specified folder
BEGIN TRANSACTION
EXEC [Builder].[RunScriptsInFolder] '$(ProjectDir)Scripts\Post-Deployment\Data\'
COMMIT TRANSACTION
---- Enable all constraints
RAISERROR ('Re-enabling all constraints...', 0, 1) WITH NOWAIT
exec sp_msforeachtable @command1="print '?'", @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
You may also wonder why $(ProjectDir) variable doesn't work for you. To enable it open your *.dbproj file with text editor and add this code at the end.
<PropertyGroup>
<SetVariables>
<Variable Name="ProjectDir" Value="$(ProjectDir)" />
</SetVariables>
</PropertyGroup>
Alternatively you can open your DB project properties, find tab Variables and add set variable ProjectDir=$(ProjectDir)