CODEWITHSUNDEEP
sql-server-2012dacpac
The Muffin Man
The Muffin Man

Reputation: 20004

Environment aware sql scripts

I'm using DACPAC's as part of my database deployment. The same package gets deployed to dev/staging/prod environments. What we'd like to be able to do is seed a database with data such as admin users which have a different userid on each environment for example.

It would be great if I could place a variable in the publish profile for the DACPAC (or other), but I imagine I could also accomplish this by looking at the @@servername. Is there a better way of accomplishing this?

Upvotes: 1

Views: 1025

Answers (1)

The Muffin Man
The Muffin Man

Reputation: 20004

So this is super clean if you're using SSDT in Visual Studio.

Go to project properties, SQLCmd tab and enter in any variables.

enter image description here

Then use them in your scripts.

enter image description here

When publishing within visual studio you'll get a nice UI that allows you to specify values for the variables or load the defaults (if you specified any).

enter image description here

The publish profile is just an xml file in the format of an MSBuild project, so there's nothing crazy going on there. If you're deploying automatically you can specify the default values for the variables with whatever dacpac deploy tool you're using. I use SqlPackage which is apart of SSDT. Command Line Reference

Usage from Powershell:

& $sqlpackageexe /Action:Publish /SourceFile:$dacpac /Profile:$dacprofile /TargetConnectionString:$connstring /Variables:DbEnvironment=Development

Upvotes: 4

Related Questions