CODEWITHSUNDEEP
sqlcommand-linedata-tier-applicationsdacpac
jaekie
jaekie

Reputation: 2303

Updating Data-Tier Application version via SqlPackage.exe

I am currently automating the deployment of my applications database via command line using SqlPackage.exe, but unable to find a way to update the version of my Data-Tier Application

(Development, of course...)

http://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

I am using the /Action:Publish to push my newly built *.dacpac, but version always remains at v1.0.0.0, if this is not possible? How about how to change an ExtendedProperty?

I just want to have a reference to what version is installed by looking at the database.

Any Ideas?

Upvotes: 0

Views: 2057

Answers (2)

Tim
Tim

Reputation: 1354

Including

/p:RegisterDataTierApplication=true

as part of the command line is what worked for me to have SqlPackage.exe update the version stored in data-tier metadata with the version of the DACPAC being published.

In my testing, I've found that setting RegisterDataTierApplication to true is performing an upgrade if the target database is already registered. Essentially, this property tells SqlPackage.exe that the DACPAC should be published as a data-tier package, as opposed to just publishing any changed objects (and therefore causing drift).

So, the full command that I use is

SqlPackage.exe /a:publish /tcs:<target connection string> /sf:<DACPAC file path> /p:RegisterDataTierApplication=true /p:BlockWhenDriftDetected=false

(I found that I had to include /p:BlockWhenDriftDetected=false because SqlPackage.exe was returning an error that it detected drift, even though /a:deployreport did not report any drift.)

Upvotes: 1

nojetlag
nojetlag

Reputation: 753

On your database project go to project settings, there is a section Output types, click on the properties button below "Data-tier Application (.dacpac) file):

In this dialog you can set the version number that then gets deployed, you can then query this information from:

SELECT TOP 1000 [instance_id]
      ,[instance_name]
      ,[type_name]
      ,[type_version]
      ,[description]
      ,[type_stream]
      ,[date_created]
      ,[created_by]
      ,[database_name]
  FROM [msdb].[dbo].[sysdac_instances]

Upvotes: 0

Related Questions