Referencing a SQLCMD variable in a Post Deployment script using SqlPackage

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  October 3, 2022 at 12:58 pm

  #4094842

  Ive got a Database project running in JetBrains Rider.

  The project publishes a DACPAC to create or update a target database.

  Now I have a set of reference data scripts which run (in Create mode) and these are reference through

  the Script.PostDeployment which looks like this:

  :r .\LoadDeviceConnectionType.sql

  :r .\LoadUser.sql

  :r .\LoadConversionType.sql

  :r .\LoadChannelType.sql

  :r .\CreateSwaggerUser.sql

  Now i want to be able to pass a SQLCMD variable called 'Deploy' so that each script will only be executed depending on

  the variable. So for example LoadUser would run like this

  If $(DeployType) = 'Create'

  BEGIN

  INSERT [dbo].[User] ([UserID], [UserName]) VALUES (NEWID(), N'Swagger')

  end

  And therefore I would run sqlpackage as follows:

  SqlPackage /Action:Publish /p:CreateNewDatabase=False /p:DropObjectsNotInSource=True /SourceFile:"C:\Users\user1\source\repos\metrology\TinytagExplorerMe

  trology\Logger_Groups.Build\bin\Debug\netstandard2.0\Logger_Groups.Build.dacpac" /TargetDatabaseName:Logger_Groups_CI /TargetServerName:"localhost\SQLEXPRESS" /v:DeployType="Create"

  However i cant get the project to compile as i get the following error

  Incorrect syntax near If.

  How can i set a SQLCMD variable in my scripts that can be called from SqlPackage?

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  October 3, 2022 at 1:09 pm

  #4094847

  Have you added the variable to the SQLCMD Variables collection in Database Properties?

  ---

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  October 3, 2022 at 1:44 pm

  #4094852

  No I havent added the variable to the SQLCMD variable collection in Database Properties

  I am using jet brains rider. Do you know how I can add the variable into the project here?

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  October 3, 2022 at 1:56 pm

  #4094858

  I have never used Jetbrains rider, so no. But as database properties are created in VS before the creation of any DACPACs, I suspect that it cannot be done.

  ---