Deploying different seed data for different publishing profiles with visual studio ssdt?

Question

Can I deploy different seed datasets for different publishing profiles using the Visual Studio Sql Server Data database project?

We know that you can expand seed data using a post-deploy script. We know that you can deploy them in different environments using the publishing tool. We do not know how you can deploy different seed data in different environments.

Why do we have to do this?

• We want to be able to do this so that we can have a small explicit set of input data for unit testing.
• We need a wider set of data for deployment in a test group environment so that the test team checks the entire application against
• We need a specific set of input data for the pre-use environment.

Answer 1

There are several ways to achieve this, the first approach is to check the environment in deploying a post script, for example ..

if @@servername = 'dev_server' begin insert data here end 

A slightly cleaner version should have different script files for each environment and import them through: r import sqlcmd script so you can:

• PostDeploy.sql
• DevServer.sql
• QAServer.sql

then

 if @@servername = 'dev_server' begin :r DevServer.sql end if @@servername = 'qa_server' begin :r QAServer.sql end 

You will need to make sure that the paths to the .sql files are correct, and you will copy them using dacpac.

You do not need to use @@ servername, you can use sqlcmd variables and pass them for each environment, which again is a little cleaner than the names of hard-coded servers.

The second approach is to configure dacpac to modify the post delpoy script with your specific environment, this is my preference and works best as part of the CI assembly, my process:

• Registration Changes
• Build Server creates dacpac
• The assembly accepts dacpac, copies the files dev, qa, prod, etc.
• The assembly replaces the post-deployment script in each with a specific env script

I am calling the scripts PostDeploy.dev.sql, PostDeploy.Qa.sql, etc. and set the Build action to "None" or they are added as "Script, Not in Build".

To replace the post-deployment script, you just need to use the .net packaging API or for some examples take a look at my Dir2Dac demo that does this and much more:

https://github.com/GoEddie/Dir2Dac

more specific:

https://github.com/GoEddie/Dir2Dac/blob/master/src/Dir2Dac/DacCreator.cs

 var part = package.CreatePart(new Uri("/postdeploy.sql", UriKind.Relative), "text/plain"); using (var reader = new StreamReader(_postDeployScript)) { reader.BaseStream.CopyTo(part.GetStream(FileMode.OpenOrCreate, FileAccess.ReadWrite)); } 

Answer 2

I solved this by writing a Powershell script that runs automatically when I publish the Exec command to the Project file.

It creates a script file that includes all the scripts found in the folder in the project (the folder is called as the target environment).

This script is then included in the post-deploy script.