Executing SQL Scripts via MSBuild Extensions Pack

MSBuild is a powerful tool right out of the box.  With the ability to manipulate files and execute external commands there is a lot of mileage a creative Build Team can squeeze from it.  However, this barely scratches the surface of what it can do.  When you add on the MSBuild Extensions Pack you open up a whole new world of tasks that improve the capabilities of the Build Team.

One of the simplest, most useful, tasks available in the Extension Pack is the SqlCmd task.  This simple task does an amazing thing.  It allows you to execute SQL commands either directly or via script files directly in your build execution.  This  opens up several possibilities for complementing the build process.  From actions such as deploying new data structures to support the build to cleaning up data or reshaping it to meet new requirements.

The example that follows is basic in nature but describes the steps necessary to use the SqlCmd task to execute SQL Scripts against your database.

1. Create an empty solution in Visual Studio and add a scripts directory to house your sql scripts.

2. Add a text file to the scripts folder with a .sql extension.

3. Modify the script to perform a basic select operation

USE MSBuildDemo

SELECT *
FROM sysObjects

4. Save the script.
5. Right click on the project in Studio and select “Unload Project”.

6. Right click on the project placeholder and chose “Edit <Project>.csproj” where <Project> is the name of your project.
7. Edit the csproj file to import the Extension Packs tasks file

<PropertyGroup>
<TPath>C:\Program Files\MSBuild\ExtensionPack\4.0\MSBuild.ExtensionPack.tasks</TPath>
</PropertyGroup>
<Import Project="$(TPAth)" />

9. Reference the script files
<ItemGroup>
<InputFile Include="$(SolutionDir)Scripts\**\*.sql" />
</ItemGroup>

10. Add the SqlCmd task to call the script to the BeforeBuild target.
<MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="MySQLServer\SqlExpress" Database="MSBuildDemo" InputFiles="@(InputFile)" LogOn="BuildUser" Password="password" />
11. Save the csproj file.
12. Right click on the project place holder and click the “Reload Project” option.
13. Rebuild the solution.
14. The SQL Commands will execute as part of the build process.

Leave a Reply