1

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.

Adamon
  • 484
  • 9
  • 21

1 Answers1

4

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

Ed Elliott
  • 6,666
  • 17
  • 32
  • Hi Ed, Thanks for the suggestions, i am using sqlpackage.exe as stated in the question. I'll give the SQLCMD variables route a go as my biggest issue with the second option is how to open the dacpac and edit the post deploy file. It's simple enough to manually edit the file to a .zip and then edit the postdeploy.sql but it seems this would be somewhat more difficult using octopus deploy. To clarify: "v:variable_name=" if i put that in my sqlpackage,exe command line it will change the variable on deploy? Thanks again! Adam – Adamon Feb 02 '16 at 09:49