Export/BACPAC fails when sql_variant contains nvarchar with UTF-8 collation (Latin1_General_100_CI_AS_SC_UTF8)
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
# DacFx export fails when `sql_variant` contains a string value with UTF-8 collation
## Suggested title
`Export/BACPAC fails when sql_variant contains nvarchar with UTF-8 collation (Latin1_General_100_CI_AS_SC_UTF8)`
## Summary
`SqlPackage /Action:Export` fails during the data phase when a `sql_variant` column contains a string value whose collation is `Latin1_General_100_CI_AS_SC_UTF8`.
`/Action:Extract` succeeds against the same database, so this appears to be a data-export serialization issue rather than a schema-model issue.
The same failure was also observed via the built-in Azure SQL Database export flow, which surfaces only the generic wrapper error:
`The given key was not present in the dictionary. Could not export schema and data from database.`
This appears distinct from `microsoft/DacFx#370`, which reports a Synapse extract failure with `SQL73068: Unsupported collation 'Latin1_General_100_CI_AS_SC_UTF8'`. In this case:
- source platform is Azure SQL Database, not Synapse dedicated SQL pool
- `/Action:Extract` succeeds
- `/Action:Export` fails in the data phase inside `SqlVariantSerializer`
## Minimal repro
```sql
IF OBJECT_ID(N'dbo.DacFxSqlVariantUtf8Repro', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.DacFxSqlVariantUtf8Repro;
END
GO
CREATE TABLE dbo.DacFxSqlVariantUtf8Repro (
Id int NOT NULL CONSTRAINT PK_DacFxSqlVariantUtf8Repro PRIMARY KEY,
VariantValue sql_variant NULL
);
GO
INSERT INTO dbo.DacFxSqlVariantUtf8Repro (Id, VariantValue)
VALUES
(1, CONVERT(sql_variant, 1)),
(2, CONVERT(sql_variant, CAST(1 AS bit))),
(3, CONVERT(sql_variant, CONVERT(nvarchar(100), N'repro') COLLATE Latin1_General_100_CI_AS_SC_UTF8));
GO
SELECT
Id,
CONVERT(varchar(128), SQL_VARIANT_PROPERTY(VariantValue, 'BaseType')) AS base_type,
COALESCE(CONVERT(varchar(128), SQL_VARIANT_PROPERTY(VariantValue, 'Collation')), '') AS collation_name
FROM dbo.DacFxSqlVariantUtf8Repro
ORDER BY Id;
```
## Repro steps
1. Create or pick an empty Azure SQL Database.
2. Run the SQL in the `Minimal repro` section above.
3. Verify the inserted rows:
```sql
SELECT
Id,
CONVERT(varchar(128), SQL_VARIANT_PROPERTY(VariantValue, 'BaseType')) AS base_type,
COALESCE(CONVERT(varchar(128), SQL_VARIANT_PROPERTY(VariantValue, 'Collation')), '') AS collation_name
FROM dbo.DacFxSqlVariantUtf8Repro
ORDER BY Id;
```
Expected verification result:
```text
1 | int |
2 | bit |
3 | nvarchar | Latin1_General_100_CI_AS_SC_UTF8
```
4. Run schema extract:
```bash
sqlpackage /Action:Extract \
/SourceConnectionString:"Server=tcp:.database.windows.net,1433;Initial Catalog:;User ID=;Password=;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" \
/TargetFile:".dacpac"
```
5. Run BACPAC export:
```bash
sqlpackage /Action:Export \
/SourceConnectionString:"Server=tcp:.database.windows.net,1433;Initial Catalog:;User ID=;Password=;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" \
/TargetFile:".bacpac" \
/Diagnostics:True \
/DiagnosticsLevel:Verbose
```
## Expected behavior
`/Action:Export` should succeed, just as `/Action:Extract` does.
If the collation is unsupported, DacFx should emit a targeted validation error instead of failing with an internal dictionary lookup exception.
## Actual behavior
`/Action:Export` fails during data export with:
```text
Could not export schema and data from database.
One or more errors occurred. (The given key 'Latin1_General_100_CI_AS_SC_UTF8' was not present in the dictionary.)
```
Relevant stack trace:
```text
System.Collections.Generic.KeyNotFoundException: The given key 'Latin1_General_100_CI_AS_SC_UTF8' was not present in the dictionary.
at Microsoft.Data.Tools.Schema.Sql.SqlClient.Bcp.CollationInfo.GetInfo(String collation)
at Microsoft.Data.Tools.Schema.Sql.SqlClient.Bcp.SqlVariantSerializer.GetBytesNoLength(SqlDataReader source)
at Microsoft.Data.Tools.Schema.Sql.SqlClient.Bcp.SqlVariantSerializer.GetBytes(SqlDataReader source)
```
This strongly suggests the failure is in DacFx's `sql_variant` string serialization path for UTF-8 collations.
## Environment
- Azure SQL Database
- Observed server version: `12.0.2000.8`
- Observed service objective: `ElasticPool`
- `SqlPackage` version: `170.3.93.6`
- Observed on: macOS `26.3.1`, .NET `10.0.3`
- Azure SQL built-in export shows the same wrapped failure
## Additional observations
- Schema-only extract succeeds.
- The failure occurs in the export data phase.
- The original production database that exposed this had a `sql_variant` column with:
- `int`
- `bit`
- `nvarchar` values carrying collation `Latin1_General_100_CI_AS_SC_UTF8`
- The minimal repro above reduces that shape to a single table and one UTF-8-collated string value inside `sql_variant`.
## Impact
Any Azure SQL Database that stores UTF-8-collated string values inside `sql_variant` may be impossible to export to BACPAC using either:
- Azure SQL built-in export
- `SqlPackage /Action:Export`
## Workaround
Current workaround is to avoid exporting those `sql_variant` string values as-is, for example by:
- exporting from a sanitized copy of the database
- migrating the problematic `sql_variant` data to a non-`sql_variant` type before export
- excluding or rewriting the affected table/data before taking a BACPAC
## Request
Please add support for UTF-8 collations in the `sql_variant` export path, or emit a deterministic validation error that clearly identifies the unsupported data shape.
Contributor guide
Research direction
Start by running the minimal Azure SQL repro and SqlPackage /Action:Export to confirm the data-phase failure. Trace the stack entries for SqlVariantSerializer.GetBytesNoLength and CollationInfo.GetInfo, then determine whether the UTF-8 collation can be serialized or must receive a targeted validation error. Done means export succeeds or reports a deterministic error identifying the unsupported data shape.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- azure, csharp, sql
- Domain
- database, tooling
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 38/100