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

Question

We have some automated dacpac deployment code that handles both CreateNewDatabase correctly and simply updates database scripts in C # using Microsoft.SqlServer.Dac

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

I tried to find a suitable place in the DacDeployOptions and DacServices , but I can not find anything that would do this.

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

Alternatively, some time ago I remember seeing a sample code that showed how to traverse dacpac and create a new dacpac at runtime. I think this approach would allow me to simply create a new dacpac, which I could pass to Deploy, and which would exclude Pre and Post Deployment scripts. I do not like this solution, but it will allow me to achieve what I need.

Question 2 . Can someone point me to some examples of this?

My code is:

 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); } 

Question related to: Creating LocalDB for testing from a Visual Studio SQL project

Answer 1

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

1) create an empty project that refers to your main project using the same database link - when deploying without scripts, deploying an empty one using IncludeCompositeObjects - the scripts before and after the deployment are executed only with the dacpac that you are deploying, not from which any dacpacs links, but it’s obvious that the code and schema are deployed. This describes this:

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

2) Use the SQLCMD variables to transfer the data settings and pass the value to the deployment.

3) make your scripts check to see if they need to tune the data, for example, only insert if the rowcount table is zero

4) use merge scripts for reference data - I do not understand if this point is for reference data or test data setup

5) Use the .net wrapping api to remove the pre / post deploy scripts from dacpac, this shows you how to write scripts so you can do GetPart and not WritePart:

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

In general, I would suggest that there is probably a simpler solution - if it is for testing, maybe it can be part of the data setup in the test setup? If you are testing a device, tSQLt will help you avoid all this using FakeTable.

Hope this helps :)

Ed

Answer 2

Two attempts:

• Firstly, doing such things is quite easy if you use MSBuild , since you can configure a specific configuration to include one or more parts of the Project. The .sqlproj file contains the <ItemGroup> section, which should look something like this:

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

  You can simply add a “Condition” that will determine if this ItemGroup is used or not. You can see these "Status" attributes in the .sqlproj file (usually). The result should look like this:

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

  Then you simply switch between “Release” or “Debug” in the “Active Configuration” drop-down list, and the pre and post deploy scripts will be included or excluded accordingly.

• Another thought was to somehow reset the scripts before and after deployment. Since you are loading DacPac into dacPackage , you will have access to PreDeploymentScript and PostDeploymentScript . I cannot test, but it may be possible to “erase” what is there (provided that the threads already point to the saved scripts).