We are using Visual Studio SQL Server Data Tools (SSDT) projects more and more. Initially we were using it for getting the database under source control but now we are also using it for deploying the database even with data included.
One way to include data is to add a script file in the SSDT project with ‘insert into’ statements but because our requirement was to include a lot of (meta)data everytime the project was build we came up with another solution.
In short:
- in the post build event of the SSDT project in Visual Studio we export the data from the database to files in the build output directory with the SQL Server bcp utility
- we created a Post Deployment script in the SSDT project to import the data from the files when the database is deployed
I will describe the solution based on the AdventureWorks sample database which can be downloaded from here. I used Visual Studio 2015, SQL Server Data Tools and SQL Server 2014.
Step 1
I created a new solution with two projects. The first project is a SSDT project. For simpilicity I have only included the table Person.CountryRegion in the project and some related objects like User Defined Types and a Schema.
Step 2
In the Post-build event of the project the SQL Server utility bcp is called to export the data to a file in the build output directory.
When building the project the data is exported to the file Person.CountryRegion.dat.
Step 3
A Post-Deployment Script is added to the SSDT project.
For inserting the exported data we are using a BULK INSERT statement. In this statement a SQLCMD variable is used with the name $(DataFiles). This variable must be set to the build output directory when deploying the database so it can find the Person.CountryRegion.dat file.
The SQLCMD variable is specified in the SQLCMD Variables tab of the project properties.
Now everything is setup to create a dacpac file for deploying the database and also deploying the data.
I created a console application for testing the deployment of the dacpac file. The NuGet Package Microsoft.SqlServer.Dac is installed for the necessary assemblies to deploy a dacpac. There are also other ways to deploy a dacpac like SqlPackage.exe, PowerShell, etc.
With the DacDeployOptions class we can set the SQLCMD variable DataFile to the build output directory. When we run the console application a database with the name AdventureWorksTest is created with the table Person.CountryRegion filled with data.
The Visual Studio 2015 solution can be downloaded from GitHub. Don’t forget to set the console application as Startup Project in Visual Studio. For running this example also SQL Server Data Tools and the AdventureWorks sample database is needed.
marcelwouters 