I have an external file format defined in my sql project.
The sql target platform is Azure Synapse Analytics Serverless SQL Pool.
The file format looks like this:
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat]
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR = N';', FIRST_ROW = 2, USE_TYPE_DEFAULT = False)
);
But after building and deploying a DacPac of the project via SqlAzureDacpacDeployment@1, the resulting file format in the database ends up like this
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat]
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR = N';')
);
Notice the missing options.
The issue can also be observed by doing a schema compare, in visual studio, against a database which has the proper file format with alle the options. the FIRST_ROW and USE_TYPE_DEFAULT options will be missing when viewing the file format in the compare window.
I’ve reported this to microsoft, as i suspect its a bug on their end, but this issue is rather pressing, so i wanted to see if anyone on here has a solution or workaround.
1