Dataverse - Issue - Azure Synapse Link for Synapse Workspace does not create 4 meta data external table OptionsetMetadata StateMetadata StatusMetadata TargetMetadata

(0) Share

Report

Posted on by dp33fr

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 :

GlobalOptionsetMetadata
OptionsetMetadata
StateMetadata
StatusMetadata
TargetMetadata

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:

OptionsetMetadata
StateMetadata
StatusMetadata
TargetMetadata

Our Synapse Workspace is in North Europe.

Regards,

Denis

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

AhmedSalih

6,680 Moderator on at

Like (2)

Report

Copy link

Link copied!

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.

Right-click the database icon, then select New SQL script > Empty script.
Apply a join SQL script to join the choice metadata with your Dataverse table and store the view in a new database.

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

Was this reply helpful? Yes No

dp33fr 5 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like (0)

Report
Copy link

Link copied!

@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 😅

Was this reply helpful? Yes No