Include Scripts
Published 06 July 2016
During deployment, SQL Change Automation uses the SqlCmd utility to execute your migration scripts. Aside from enabling you to include variables in your deployments, this also allows you to include other script files from within your Pre-Deployment & Post-Deployment scripts by using the :r < filename > command.
You may want to do this if, for example, you have a script containing complex logic or large amounts of data, and you want to make the file more readable by splitting sections of it into individual files.
The ability to include files within your scripts is currently restricted to Pre-Deployment and Post-Deployment scripts, as unfortunately files cannot be included within migrations or programmable objects due to limitations within the T-SQL parser.
How it works
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 (e.g. bin\Debug) during build, so that your Continuous Integration server will pick up the included artifacts during build (i.e. for deployment onto other environments).
The file included within the above snapshot has the following content:
Contents of "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:
Contents of "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 (e.g. using a release management tool such as Octopus Deploy or TFS), note that the $(DeployPath) variable will instead resolve to the folder where the package was extracted to:
C:\Octopus\Applications\MyProject\AdventureWorks.Database\1.0.2.106\
When you deploy your solution, the included files will be copied to the output folder of your project (e.g. 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:
