I'm creating a DacPac in TeamCity by building a sql project. The resulting DacPac has a post deployment script that I would like to update either on deployment or before it is created with a version number. Is it possible to set this parameter either in TeamCity or on deployment of the DacPac?
The sqlpackage.exe command line looks like
C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /Sourcefile:#{SourceFolder} /TargetDatabaseName:DBName /TargetServerName:#{SqlServer}
Where "#{}" is a parameter on octopus deploy server. The post deployment script in the SQL Project looks like :
declare @version varchar(10)
set @version = 'z'
IF EXISTS (SELECT * FROM tVersion)
UPDATE VersionTable SET Version = @version
ELSE
INSERT INTO VersionTable VALUES (@version)
The way I have been doing it is by using file content replacer on teamcity to replace 'z' with a version number but this method is not ideal. This could lead to errors in the future if another dev were to check in the the file with a different parameter that didn't fit the regular expression used in the file content replacer build feature.
You have a couple of different approaches you can take, the first one is the easiest in that you define a SqlCmd variable in your .sqlproj (properties of the project, SQLCMD variables tab) and reference that it your post deploy script. When you deploy you can override the variable by using /v:variable_name= (If you aren't using sqlpackage.exe to deploy, what are you using? Octopus deploy?).
The second way is harder but is pretty straight forward, the dacpac can be read from and written to using the .net packaging api, there is a stream (file) called postdeploy.sql (open it as a zip file and it is obvious which one is the post deploy file), you can read it, change your specific value and then write it back again.
For more manual editing of a dacpac see:
https://github.com/GoEddie/Dacpac-References
Ed