Azure Synapse Failure

Question

Using the below code I am trying to copy the data from this code into a dedicated table. The data is coming from a Parquet that I need to alter before loading it into the table. I get the below error.

Operation on target Move InvItemDist to DP failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'FORMAT'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'FORMAT'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'FORMAT'.,},],'

Code:

    SELECT 
    A.[NDC]
    , A.[ProductID]
    , A.[Customer_Site]
    , A.[ZipFileName]

   FROM 
    (
        SELECT
            DISTINCT
                [NDC]
                , [ProductID]
                , [Customer_Site]
                , LEFT
                    (
                        SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))
                        , LEN(SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))) - 4
                    ) AS [ZileFileOrder]
                , [ZipFileName]
                , ROW_NUMBER() OVER (PARTITION BY [NDC], [ProductID], [Customer_Site] 
                        ORDER BY LEFT
                    (
                        SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))
                        , LEN(SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))) - 4
                    ) DESC) AS UN

        FROM
            OPENROWSET(
                BULK 'https://stonexiacctolleson3154ba.dfs.core.windows.net/fs-tolleson/InvItemDist - Fact/2022/06/15/*.parquet',
                FORMAT = 'PARQUET'
            ) AS [result]
    ) AS A
WHERE A.[UN] = 1

Answer 1

OPENROWSET() is currently not supported in synapse dedicated pool.

You can raise a feature request from the portal.

Use External file format to access the data from the files. You can refer to this Microsoft document to create an External file format to read the parquet files