SqlPackage extracttarget (output format) -> SQL project

[dzsquared]

Is your feature request related to a problem? Please describe.
Creating a SQL project using SqlPackage/DacFx is possible using the extracttarget property set to SchemaObjectType, ObjectType, Schema, or Flat - which generates .sql scripts for all the objects and organizes them according to the setting. However - its missing database settings/properties and the actual SQL project file.

Describe the solution you'd like
SqlPackage to be able to generate a complete SQL project.

Describe alternatives you've considered
dotnet new sqlproj (Microsoft.Build.Sql.Templates)

open questions to folks on the overall behavior:

• should this be a change applied to the existing related extract targets (SchemaObjectType, ObjectType, Schema, or Flat ) or a wholy new extract target SqlProject?
• do you have a scenario where the presence of a .sqlproj alongside the .sql files will create friction in using the output?

[Johannes-Vink]

I love it.

1. Make it such a way that agents can automatically move structures from repo to database and the other way around and that agents understand such new functionality as it is not part of the common knowledge of the moment. So either proper documentation, -help flag etc.

2. Definitely make it possible to filter upfront for example on schema so that the extract time is not that long. For example a Azure SQL DB does only have 1 db and needs to split out via schema's...

[jasonhorner]

My main ask here is to provide an option to ignore things that would cause a dacpac or sqlproj to break.. the ignored objects would be available in a list so they can either be remediated or removed... schema filters as well as object filters would useful as well... I could see all of these features being used to support a migration scenario or to create test environments.

[ErikEJ]

@dzsquared

Excellent idea!

Please add new targets, I use the DacFX API in SQL Database Projects Power Tools for this to extract files for use with our community SDK, and it would be breaking if I will now have to remove a .sqlproj file from the generated output.

We also guide our users to use extract with sqlpackage and adding a .sqlproj project file will also add confusing in that scenario.

Just add more targets:

SchemaObjectTypeSqlProject
ObjectTypeSqlProject
SchemaSqlProject
FlatSqlProject

[dzsquared]

@ErikEJ - perfect - that's what I suspected, the current utilization of the extract targets would fracture with the addition of a sqlproj.

All - Should we be opinionated about the correct file organization in a SQL project and avoid adding options that are unlikely to be used (FlatSqlProject comes to mind) or do you really like being able to organize your SQL project in other structures than SchemaObjectType?

[ErikEJ]

@dzsquared I would be fine with just SqlProject defaulting to SchemaObjectType.

[ErikEJ]

I also have some quite quirky code to get the database settings, would be really nice if there were a better general way / format to get these

https://github.com/ErikEJ/SqlProjectPowerTools/blob/main/src%2FDacFXToolLib%2FSchemaExtractor.cs#L32