1

I'm attempting to use the SQL Server Database Deploy release task to deploy my dacpac and I would like to use the /p:DoNotDropObjectTypes=Users;Permissions argument to prevent certain objects from being dropped, however I'm getting the following error below:

2018-08-13T15:48:30.2644849Z ##[section]Starting: Deploy using : dacpac
2018-08-13T15:48:30.2655188Z ==============================================================================
2018-08-13T15:48:30.2655399Z Task         : SQL Server Database Deploy
2018-08-13T15:48:30.2655603Z Description  : Deploy to SQL Server Database using DACPAC or SQL scripts
2018-08-13T15:48:30.2655749Z Version      : 0.3.11
2018-08-13T15:48:30.2655878Z Author       : Microsoft Corporation
2018-08-13T15:48:30.2656030Z Help         : [More Information](https://aka.ms/sqldacpacmachinegroupreadme)
2018-08-13T15:48:30.2656531Z ==============================================================================
2018-08-13T15:48:36.9603826Z *** 'DoNotDropObjectTypes' is not a valid argument for the 'Publish' action.
2018-08-13T15:48:37.2122455Z ##[error]System.Management.Automation.RuntimeException
2018-08-13T15:48:37.2249276Z ##[section]Finishing: Deploy using : dacpac

I also tried this with my publish profile sans setting the additional arguments in the task:

<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropPermissions>True</DoNotDropPermissions>
<DoNotDropRoleMembership>True</DoNotDropRoleMembership>
<DoNotDropUsers>True</DoNotDropUsers>

but SqlPackage tried to drop them anyway:

Dropping Permission... Dropping Permission... Dropping Permission... Dropping Permission... Dropping Permission... Dropping Permission... Dropping Permission...
Joe Eng
  • 1,072
  • 2
  • 15
  • 30
  • What version of SSDT is installed on your build environment? – Daniel Mann Aug 13 '18 at 16:09
  • I forgot to mention we are deploying to an on-prem server using deployment groups. Does that server need SSDT installed? – Joe Eng Aug 13 '18 at 16:17
  • I think SQL Server Data Tools need to be installed where your agent is running. Check here [https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de13bbf5-6da3-4064-8ee3-465393d84f08/sqlpackageexe-compalins-about-pdonotdropobjecttype?forum=sqltools] and here [https://blogs.msdn.microsoft.com/ssdt/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop/#comments] – Michele Ferracin Aug 13 '18 at 21:19
  • @MicheleFerracin I saw those links, but it's not clear if SSDT installation is required on the target machine. Also, I am assuming the most recent version of SSDT is already installed on the VSTS build agent, but I'm not sure how to verify that. – Joe Eng Aug 13 '18 at 21:32
  • Yes, it's needed on the machine where the agent is running. I think you can check if SSDT is installed by looking at the list of appication installed Control Panel -> Program And Features. – Michele Ferracin Aug 14 '18 at 08:03
  • Please try it by calling sqlpackage.exe tool from build/release agent directly and check the result. (Path likes C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin) – starian chen-MSFT Aug 15 '18 at 11:07
  • I tried this and passed the publish profile, but it said path not found: %programfiles(x86)%\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe – Joe Eng Aug 21 '18 at 17:22
  • I installed the latest version of DacFX https://www.microsoft.com/en-us/download/details.aspx?id=56508 on our on-prem VSTS agent and that fixed it. I can also confirm SqlPackge did respect the publish profile and the do not drop settings within it. @MicheleFerracin, if you add your comment as an answer I'll accept it. – Joe Eng Aug 22 '18 at 19:08

1 Answers1

0

I think SQL Server Data Tools need to be installed where your agent is running. Check this MSDN article and this blog post .

Michele Ferracin
  • 397
  • 3
  • 8