SqlCmdVariable support

[molszews]

Seems like there is no way to create dacpac that accepts params:

you only need to have sql like that:
INSERT INTO $(TasksDb).[schema].[table] ...

so you can pass the variable during runtime:
sqlpackage.exe /Action:Publish /SourceFile:pkg.dacpac /Target(..) /v:TasksDb=Tasks

btw such params are present in sqlproj as well:

    <SqlCmdVariable Include="TasksDb">
      <DefaultValue>Tasks</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>

[jmezach]

Hi @molszews, thanks for reporting this. This currently doesn't seem to be supported, but would be a great addition. We would probably have to add those SQLCMD variables to the .csproj and then pass that as arguments to the BuildDacpac tool so that it can add them to the model. That will require some time to figure out though, which I'm pretty short off at the moment. Would appreciate a pull request for this though.

[ErikEJ]

		<CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
			<Metadata Name="DbUserPassword" Value="" />
		</CustomData>

[jmezach]

Hmm, if that's part of the CustomData it is probably going to be a little bit harder than I thought. For some reason those aren't exposed on the public API of the DacFx model. I had the same issue with referenced .dacpac's, but managed to do some reflection to make that work.

[ErikEJ]

I already have it working in PackageBuilder class, but there is a long pipeline of passing parameters etc. I can submit a PR for this, if you are intserested?

        public void AddSqlCmdVariable(string variableName)
        {
            // Ensure that the model has been created
            EnsureModelCreated();

            Console.WriteLine($"Adding sqlcmd variable {variableName}");
            Model.AddSqlCmdVariable(variableName);
        }

[jmezach]

Yeah, that's pretty much the bulk of what this project does, passing parameters around ;). That already looks like a good start though. We'll probably need to add an argument to the BuildDacpac tool that can be parsed and passed to the PackageBuilder. Then we'll need to modify the MSBuild logic to pass those command line arguments to BuildDacpac. Preferably we'll allow to add this to the project file using a similar syntax as with .sqlproj.

[ErikEJ]

Would it be an option to simply treat SqlCmdVariables like other properties?

(And then of course special case when adding this property)

<Project Sdk="MSBuild.Sdk.SqlProj/1.0.0">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlCmdVariable>DbUserPassword</SqlCmdVariable>
    </PropertyGroup>
</Project>

[jmezach]

I'm not entirely sure that is going to work. I don't think you can have the same property defined multiple times, so if you have multiple SQLCMD variables you want to define you'll need to separate them with semi-colons. Also, how are we going to define the value and default value?

Also, having an easy way to migrate from .sqlproj to .csproj has been sort of a design goal for me, so that's why I would prefer to do it the way that @molszews showed in his example. I think it will even make the MSBuild logic easier and cleaner tbh.

[ErikEJ]

Agree - though I am not sure the value and defaultvalue are need in the dacpac - they are for VS use only.

[ErikEJ]

From https://docs.microsoft.com/en-us/sql/ssdt/database-project-settings?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev16.query%3FappId%3DDev16IDEF1%26l%3DEN-US%26k%3Dk(sql.data.tools.advanceddeploymentsystemdefined.dialog)%26rd%3Dtrue&view=sql-server-ver15#bkmk_sqlcmd_variables

SQLCMD Variables

In SQL Server Database Projects you can utilize SQLCMD variables to provide dynamic substitution to be used for debugging or publishing. You enter the variable name and values and during build, the values will be substituted. If there are no local values, the default value will be used. By entering these variables in project properties, they will automatically be offered in publishing and are stored in publishing profiles. You can pull in the project values of the variables into publish via the Load Values button.
Make sure the right variables are entered in project properties, because these variables are not validated against a script in the project, nor are the variables used in script automatically populated.
Additionally, command line publishing enables you to override these values at the command line or using a profile.

[ErikEJ]

So compatible syntax would be:

  <ItemGroup>
    <SqlCmdVariable Include="DbReaderPassword" />
    <SqlCmdVariable Include="DbUserPassword" />
  </ItemGroup>

[jmezach]

Thanks to the effort of @ErikEJ we now have a prerelease version available that supports SQLCMD variables. It would be great if @molszews and @ErikEJ could try this out in a real world project, since I'm not using SQLCMD variables myself. I have seen them ending up in the resulting .dacpac though, so I'm fairly confident it will work. I've settled on the original syntax as seen here in the original issue, see this example project.

Please let me know of this works for you so I can close this issue. I'll also add it to the README.

[molszews]

I think this is impossible to test atm, seems to me that SQLCMD support (incl variables) is limited to post/pre deployment scripts which are subject of the different issue

[ErikEJ]

You can also use them in model scripts - https://sqlplayer.net/2019/03/tokenization-of-database-project-in-ssdt/ - but we only use them in post deploy script.

[jmezach]

It looks like the pre- and postdeployment scripts are exposed through the.DacPackage class. However, I haven't yet found a way to set them when building the package. That API seems to be readonly.

[ErikEJ]

I will implement this: https://github.com/GoEddie/DacpacMerge/blob/f37ec0edbc1f77860bd745cd28d9c7d7030491e7/src/MergeEm/Program.cs#L112

[jmezach]

That looks like a good place to start. I just did a little experiment with something similar and it looks like the script file ends up in the package. Haven't tested yet if it also gets executed at deployment time though. But if that works, we'll only need to figure out how we can support referencing other files. I'll try to do some more research on that when I have the time.

[ErikEJ]

Maybe we could skip supporting multiple files for now? see #9 (comment)

[jmezach]

1.1.0 is out now. Grab it while it's hot ;). I'll go ahead and close this issue.

Thanks so much @ErikEJ for helping out with this. How would you feel about becoming a maintainer on this project?

[ErikEJ]

I would be honoured to become a maintainer! It's a great project.

And I plan to blog about it soon, as it is currently semi ready for prime time.

[jmezach]

Cool. I've sent you an invite.