SqlPackage: Add option to exclude or only include certain object types (to enable use with Fabric read only SQL endpoints)

[bgribaudo]

Is your feature request related to a problem? Please describe.
In Fabric, a lakehouse automatically receives a read only SQL endpoint. In this SQL endpoint, the user can create non-table objects, such as views or functions. However, tables cannot be created. This is because the endpoint's tables are auto-managed by the lakehouse.

Problem I encountered was when I tried to use SqlPackage to move the objects I created between two endpoints. When assembling its output, SqlPackage wanted to include tables. However, in the context of a read-only endpoint, this is inappropriate because tables cannot be directly created by users (including user tools like SqlPackage).

Describe the solution you'd like
For SqlPackage to offer a command-line option that allows the user to specify which object types to include or to ignore. This way, when using SqlPackage with a read only endpoint, I could tell it to ignore all tables.

Example:

• /ignoretype:table (would include all object types but table)
• /includetype:view (would ignore all object types except for views)

[ErikEJ]

@bgribaudo Which sqlpackage action did you run? and which sqlpackage version?

[dzsquared]

First - acknowledging that "read only" endpoints are flawed in that they aren't actually read-only, since you can create views and functions on them.

Second - a suggested solution for you. Use SqlPackage extract and publsh, never import/export. You can include data by using the property /p:ExtractAllTableData=true if you really want it.
Now for publishing, you can use the property /p:ExcludeObjectType multiple times or its cousin /p:ExcludeObjectTypes to specify which objects are in your dacpac that you don't want to deploy.
Reference properties: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16

[bgribaudo]

Sorry for the delay in response. Hope you all had a great Christmas!

Which sqlpackage action did you run? and which sqlpackage version?
162.5.57.1

Extract

[bgribaudo]

Thanks, @dzsquared. That is a great idea. Didn't realize that option existed on publish.

[bgribaudo]

In the big picture, given that Fabric's "read only" SQL endpoints have widespread use and probably won't be going anywhere anytime soon, would it be within scope to enhance SqlPackage to gracefully handle them?

@dzsquared points out that there is a way to get SqlPackage working with them, but the work around can take a little digging to figure out.