How to disable predeployment and postdeployment scripts in DacServices.Deploy()

Question

We have some automated dacpac deployment code which correctly handles both a CreateNewDatabase and straightforward Update database scenarios in C# using Microsoft.SqlServer.Dac

Now in the CreateNewDatabase case we want to be able to run the DacServices.Deploy() with both the Pre and Post Deployment scripts disabled. I.e. they should not be executed in this scenario.

I have tried to find a suitable place in the DacDeployOptions and DacServices objects but cannot find anything that will do this.Ideally

Question 1: I would like something like DacDeployOptions.IgnorePreDeploymentScript = true Is there any means by which I could achieve this at runtime?

As an alternative, some time ago I remember seeing example code which showed how to traverse a dacpac and create a new dacpac in run time. I think this approach would allow me to simply create a new dacpac which I could pass to the Deploy and which would exclude the Pre and Post Deployment scripts. I don't like this solution but it would allow me to achieve what I need.

Question 2: Can anyone point me to some examples for this please?

My code:

var dacService = new DacServices(ConstDefaultConnectionString);
using (var dacPackage = DacPackage.Load(dacPacFilePath))
{
    var deployOptions = new DacDeployOptions 
    { 
       CreateNewDatabase = true, 
       IncludeTransactionalScripts = false
    };
    dacService.Deploy(dacPackage, TestDatabaseName, true, deployOptions);
}

The question is related to: Create LocalDB for testing from Visual Studio SQL project

Cupellation

Answer 1

There are a number of approaches you can take for this, this is a bit of a brain dump (hey the clocks went back last night and I'm not even sure if the current time):

1) create an empty project that references your main project using a same database reference - when you deploy without the scripts deploy the empty one using IncludeCompositeObjects - pre/post deploy scripts are only run from the dacpac you deploy not from any referenced dacpacs but obviously the code and scheme are deployed. This describes it:

https://the.agilesql.club/blog/Ed-Elliott/2016-03-03/Post-Deploy-Scripts-In-Composite-Dacpac-not-deploying

2) use SQLCMD variables to wrap the data setups and pass in the value to the deploy.

3) make your scripts check for whether they should setup data like only insert if the table rowcount is zero

4) for reference data use merge scripts - I'm not clear if the point of this is for reference data or setting up test data

5) Use .net packaging api to remove the pre/post deploy scripts from the dacpac, this shows you how to write the scripts so you should be able to do a GetPart rather than WritePart:

https://github.com/GoEddie/Dir2Dac/blob/master/src/Dir2Dac/DacCreator.cs

On the whole I would guess that there is probably a simpler solution- if this is for testing then maybe make the data setup part of the test setup? If you are unit testing tSQLt helps you avoid all this by using FakeTable.

Hope it helps :)

Ed

Recriminate

Answer 2

Two things to try:

1. First, doing this type of thing is quite easy if you are using MSBuild since you can tailor a particular Configuration to include one or more pieces of the Project. In your .sqlproj file there is an <ItemGroup> section that should look similar to the following:

   <ItemGroup>
     <PreDeploy Include="Script.PreDeployment1.sql" />
     <PostDeploy Include="Script.PostDeployment1.sql" />
   </ItemGroup>
   

   You can simply add a "Condition" that will determine if that ItemGroup is used or not. You can see these "Condition" attributes throughout the .sqlproj file (usually). So the result should look similar to:

   <ItemGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
     <PreDeploy Include="Script.PreDeployment1.sql" />
     <PostDeploy Include="Script.PostDeployment1.sql" />
   </ItemGroup>
   

   Then you just flip between "Release" or "Debug" in the "Active Configuration" drop-down and the pre and post deploy scripts will be included or excluded accordingly.

2. The other thought was to somehow reset the pre and post deployment scripts. Since you are loading the DacPac into dacPackage, you will have access to the PreDeploymentScript and PostDeploymentScript properties. I am not able to test, but it might be possible to "erase" what is there (assuming that the streams already point to the stored scripts).

Maestricht

Looked at clearing the streams, but they are get only – Rosenkrantz

@Rosenkrantz Yes, that property is get-only, but that is just getting a reference to the stream itself. The documentation states that the stream can be used for reading and writing. So you should be able to use a StreamWriter with it. Something like: stream _PreScript = _MyDacPackage.PreDeploymentScript; and then use _PreScript to set the base stream to a length of 0, or clear it, or something like that. Does that make sense? – Maestricht

dacPackage.PreDeploymentScript.SetLength(0); executes without error, but the stream length afterwards is still the same positive number, and the actual script content is still part of the GenerateDeployScript output. – Offertory

@CeeMcSharpface So the PreDeployment script is still included? I wonder if calling Flush() on it first would help. If not, I wonder if you can simply Close() it and not have it error on trying to read from a closed stream? – Maestricht

yes it is still included. I tested that carefully; there is another series of stackoverflow postings about how to clear a MemoryStream (setlength, flush before, ...) but no avail. Also the GetBuffer extension trick did not work. Eventually I solved it as per my own answer. – Offertory

@CeeMcSharpface Ok, and thanks for doing that testing and sharing that additional feedback! And thanks for posting your solution (I had not noticed that there was a new answer here). – Maestricht

Answer 3

DACPACs are ZIP files. Use the functionality of the System.IO.Packaging namespace to remove pre- and post-deployment scripts from an existing package.

using System.IO.Packaging;

// [...]

using (var dacPac = Package.Open(dacPacFile))
{
    var preDeploy = new Uri("/predeploy.sql", UriKind.Relative);
    if (dacPac.PartExists(preDeploy))
    {
        dacPac.DeletePart(preDeploy);
    }

    var postDeploy = new Uri("/postdeploy.sql", UriKind.Relative);
    if (dacPac.PartExists(postDeploy))
    {
        dacPac.DeletePart(postDeploy);
    }

    dacPac.Close();
}

The file is simply overwritten after Close, so consider copying it away first, in case you want the original unchanged.

(this is partly covered by the internet resource linked in item 5) in the accepted answer; however the code shown above is all you need)

Offertory

Nice. Sad that the DACPAC has to be modified just to temporarily exclude the Pre- and/or Post- Deployment scripts. So much else is configurable that it's almost strange that disabling these isn't. Thanks for sharing! – Maestricht