Using include scripts in Visual Studio
Published 14 August 2019
Create a folder in the root of your project called Includes and add any files that you wish to reference from your scripts.
Select the added files within the Solution Explorer and set the Copy to Output Directory property to Copy always.
This step ensures that the file is copied to the output folder (for example, bin\Debug) during build, so that your continuous integration server will pick up the included artifacts during build (that is for deployment onto other environments).
The file included within the above snapshot has the following content:
Includes\sayhello.sql
print 'hello world!';
Then, add a script to the pre-deployment or post-deployment folder (in the above example, the file is called Post-Deployment\01_Include_External_File.sql).
Within the file, add the :r <filepath> directive, where <filepath> is a path to the file that you would like to reference from within the script. To resolve the base path to your file, include the $(DeployPath) built-in variable in the file path:
Post-Deployment\01_Include_External_File.sql
PRINT 'The following output is from an external file:' :r $(DeployPath)Includes\sayhello.sql GO
DeployPath variable
When you deploy the project in Visual Studio, you’ll notice that the value of the $(DeployPath) variable will resolve to the root folder of your SQL Change Automation project: C:\source\samples\AdventureWorks\.
However when you package the project and subsequently deploy the database (for example, using a release management tool such as Octopus Deploy or Team Foundation Server), note that the $(DeployPath) variable will instead resolve to the folder where the package was extracted to.
When you deploy your solution, the included files will be copied to the output folder of your project (for example, bin\Debug); the same place that the deployment script is copied to (and subsequently executed from) during build.
The output from both will appear in the Output window in Visual Studio:
