question

PaulHernandez-8067 avatar image
0 Votes"
PaulHernandez-8067 asked TomPhillips-1744 commented

SQLPackage.exe publish properties not working as expected

Hi everyone,
I am deploying a database project using the generated DACPAC and SQLPackage.

I am facing an unexpected behaviour when excluding object types while publishing.

If I enter the excluded objects using the "plural" property ExcludeObjectTypes, the deployment is failing.

Example (not working):

 sqlpackage /Action:Publish /p:ExcludeObjectTypes=Users;Permissions

Error message: Permissions: command not found


However, if I specify the objects individually with the "singular" property, then it works

 sqlpackage /Action:Publish /p:ExcludeObjectType=Users /p:ExcludeObjectType=Permissions

I would like to know if I am doing something wrong or there is a bug in the tool.

Target database is a Synapse Analytics SQL Pool Database and SQLPackage version is the latest.

Kind regards,
Paul

sql-server-generalazure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 commented

Try:

 sqlpackage /Action:Publish /p:ExcludeObjectTypes="Users;Permissions"

You are running sqlpackage via PowerShell which uses ; as a command seperator.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TomPhillips-1744 ,
I accepted your answer with one remark, it worked with the double quote surrounding the semicolon-separated list of object types. The remark: I'm using a script step in a devops pipeline that runs on Ubuntu. According to the documentation the script should run using Bash and not PowerShell.

0 Votes 0 ·

Bash also uses ; to separate commands.

1 Vote 1 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

From the article "SqlPackage Publish parameters, properties, and SQLCMD variables", you can see the difference between ExcludeObjectType and ExcludeObjectTypes. When you try to exclude an object, you should use ExcludeObjectType=Object_Type. If you want to exclude multiple object types, you need to use ExcludeObjectTypes=List_of_Objects_Delimited_by_Semicolon.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GuoxiongYuan-7218 ,
I followed exactly this article but is not working as described, or at least as I understand it. This is why I am asking this question.
BR. Paul

0 Votes 0 ·

You only want to exclude one object type which is Users, so you need to use

/p:ExcludeObjectType=Users

not

/p:ExcludeObjectTypes=Users

If you want to exclude Logins and Users, you can use

/p:ExcludeObjectTypes=Logins;Users

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered PaulHernandez-8067 commented

Hi @PaulHernandez-8067,

It seems that this will work find if you use a comma instead of a semicolon-delimited, please try it. Also, you could get more information from this link, see below:
83082-comma.png


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



comma.png (32.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm using a script step in a devops pipeline that runs on Ubuntu and tried with comma as separator but it did not work. The problem is of course related to multiple script plattforms but the solution was the one I accepted above.

0 Votes 0 ·