Hi,
We're testing Azure Synapse Link with the goal of replacing DES.
Azure Synapse Link successfully created all external tables in our Serverless SQL workspace except for 4 tables:
OptionsetMetadata
StateMetadata
StatusMetadata
TargetMetadata
The Data Lake directory folder for those tables is: dataverse-organisation-uid/OptionsetMetadata/
In this same folder, there are 5 CSV files :
In our SQL Serverless Lake database, we only have an external table GlobalOptionsetMetadata.
Where are the other 4 tables?
See: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choice-labels
We also reconfigured everything by deleting Azure Synapse Link (along with all Datalake CSV files etc. as mentioned in the Microsoft document) and then recreating Azure Synapse Link.
The 4 external tables are still not present!
I think that Azure Synapse Link creation process forgot to create the 4 other tables:
Our Synapse Workspace is in North Europe.
Regards,
Denis
@Dp33fr, first of all, let me say I am very thankful that you have shared the final solution with the community 😊. Secondly, now I have another person to tag when we get a question related to those OptionSets tables in the Synapse 😅
Thanks!
The rigth solution is :
- Create a new database in Serverless
- create the 4 externals tables.
Example for OptionSetMetadata :
-- OptionSetMetadata
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat]
WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
USE_TYPE_DEFAULT = FALSE,
STRING_DELIMITER = '"'
))
GO
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'dataverse-<YOURDATAVERSE>_dfs_core_windows_net')
CREATE EXTERNAL DATA SOURCE [dataverse-<YOURDATAVERSEdatasource>_dfs_core_windows_net]
WITH (
LOCATION = 'abfss://dataverse-<YOURDATAVERSE>.dfs.core.windows.net'
)
GO
CREATE EXTERNAL TABLE [dbo].[OptionSetMetadata] (
[EntityName] nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8
,[OptionSetName] nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8
,[Option] int
,[IsUserLocalizedLabel] bit
,[LocalizedLabelLanguageCode] int
,[LocalizedLabel] nvarchar(350) COLLATE Latin1_General_100_CI_AS_SC_UTF8
)
WITH (
LOCATION = 'OptionsetMetadata/OptionsetMetadata.csv',
DATA_SOURCE = [dataverse-<YOURDATAVERSE>_dfs_core_windows_net],
FILE_FORMAT = [SynapseDelimitedTextFormat]
)
GO
SELECT TOP 100 * FROM [dbo].[OptionSetMetadata]
GO
Hello, @Dp33fr, After creating an Azure Synapse Link, the following five tables are created in a folder named OptionsetMetadata in Azure Data Lake Storage Gen2.
Do you see them in the Data Lake Storage Gen2?
To consume Dataverse choice columns with serverless SQL pool.
Note: In one of my previouse integration, we only used the GlobalOptionsetMetadata to get all the Dataverse tables Choice columns label values.
If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more. I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends. Visit my Blog: www.powerplatformplace.com
|