Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Answered

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

(0) ShareShare
ReportReport
Posted on by 5

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:
  • AhmedSalih Profile Picture
    AhmedSalih 6,678 on at
    Re: Dataverse - Issue - Azure Synapse Link for Synapse Workspace does not create 4 meta data external table OptionsetMetadata StateMetadata StatusMetadata TargetMetadata

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

  • dp33fr Profile Picture
    dp33fr 5 on at
    Re: Dataverse - Issue - Azure Synapse Link for Synapse Workspace does not create 4 meta data external table OptionsetMetadata StateMetadata StatusMetadata TargetMetadata

    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
  • Verified answer
    AhmedSalih Profile Picture
    AhmedSalih 6,678 on at
    Re: Dataverse - Issue - Azure Synapse Link for Synapse Workspace does not create 4 meta data external table OptionsetMetadata StateMetadata StatusMetadata TargetMetadata

    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?

    azure-synapse-link-choice-tables.png

    To consume Dataverse choice columns with serverless SQL pool.

     

    1. Right-click the database icon, then select New SQL script > Empty script.
    2. 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


     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,526

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard

Featured topics