'Azure Synapse Serverless CETAS error "External table location is not valid"

I'm using Synapse Serverless Pool and get the following error trying to use CETAS

Msg 15860, Level 16, State 5, Line 3
External table location path is not valid. Location provided: 'https://accountName.blob.core.windows.net/ontainerName/test/'

My workspace managed identity should have all the correct ACL and RBAC roles on the storage account. I'm able to query the files I have there but is unable to execute the CETAS command.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity WITH IDENTITY = 'Managed Identity'
GO

CREATE EXTERNAL DATA SOURCE MyASDL
WITH (    LOCATION   = 'https://accountName.blob.core.windows.net/containerName'
   ,CREDENTIAL = WorkspaceIdentity)
GO

CREATE EXTERNAL FILE FORMAT CustomCSV  
WITH (  
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (ENCODING = 'UTF8')    
);  
GO

CREATE EXTERNAL TABLE Test.dbo.TestTable
WITH (
LOCATION = 'test/',  
DATA_SOURCE = MyASDL,
FILE_FORMAT = CustomCSV  
) AS
WITH source AS
(
SELECT
    jsonContent
    , JSON_VALUE (jsonContent, '$.zipCode') AS ZipCode
FROM
    OPENROWSET(        
        BULK '/customer-001-100MB.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x00',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '\n',
        DATA_SOURCE = 'MyASDL'
    )
    WITH (
        jsonContent varchar(1000) COLLATE Latin1_General_100_BIN2_UTF8
    ) AS [result]
)

SELECT ZipCode, COUNT(*) as Count 
FROM source
GROUP BY ZipCode
;

If I've tried everything in the LOCATION parameter of the CETAS command, but nothing seems to work. Both folder paths, file paths, with and without leading / trailing / etc.

The CTE select statement works without the CETAS.

Can't I use the same data source for both reading and writing? or is it something else?

Solution 1:^[1]

The issue was with my data source definition.

Where I had used https:\\ when I changed this to wasbs:\\ as per the following link TSQL CREATE EXTERNAL DATA SOURCE

Where it describes you have to use wasbs, abfs or adl depending on your data source type being a V2 storage account, V2 data lake or V1 data lake

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	CodeMonkey

'Azure Synapse Serverless CETAS error "External table location is not valid"

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]