0

Good morning everybody!

Me and my team managed to create part of an Azure Synapse pipeline which selects the database and creates a data source named 'files'. Now we want to create a view in the same pipeline using a Script activity. However, this error comes up:

Error message here

Even if we hardcoded the folder names and the file name on the path, the pipeline won't recognise the existance of the file in question.

This is our query. If we run it manually on a script in the Develop section everything works smoothly:

CREATE VIEW query here

We expected to get every file with ".parquet" extension inside every folder available on our data_source named 'files'. However, running this query on the Azure Synapse Pipeline won't work. If we run it on a script in Develop section, it works perfectly. We want to achieve that result.

Could anyone help us out? Thanks in advance!

Mateo Estrada
  • 15
  • 3

1 Answers1

0

I tried to reproduce the same thing my environment and got error.

enter image description here

The cause of error can be the Your synapse service principal or the user who is accessing the storage account does not have the role of Storage Blob data Contributor role assigned to it or your External data source have some issue. try with creating new external data source with SAS token.

Sample code:

CREATE  DATABASE  SCOPED  CREDENTIAL SasToken
WITH  IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS token';
GO

CREATE  EXTERNAL  DATA  SOURCE mysample1
WITH ( LOCATION = 'storage account',
CREDENTIAL = SasToken
)

CREATE  VIEW [dbo].[View4] AS  SELECT [result].filepath(1) as [YEAR], [result].filepath(2) as [MONTH], [result].filepath(3) as [DAY], *
FROM
OPENROWSET(
BULK  'fsn2p/*-*-*.parquet',
DATA_SOURCE = 'mysample1',
FORMAT = 'PARQUET'
) AS [result]

Execution:

enter image description here

Output:

enter image description here

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11
  • Hello! Thank you so much for your answer. We will definitely try what you suggest, as our users already have the Storage Blob data contributor. I was wondering: which linked service are you using? We are using a GET request to create files on our Data Lake (Gen2) and then we use Azure SQL Server as our Linked Service. We can use a Get Metadata to bring the name of the folders we have, but we cannot get to bring the files. Is there anything else you can suggest? – Mateo Estrada Jan 06 '23 at 20:39
  • I used Synapse analytics linked service as you have not mentioned what you are actually using. but solution will also work with Azure SQL database. – Pratik Lad Jan 11 '23 at 07:20
  • `We can use a Get Metadata to bring the name of the folders we have, but we cannot get to bring the files. Is there anything else you can suggest?` to bring files you have to iterate through each folder again inside Foreach loop activity. if you want more information on it, you can post it as new question that will help other community members also. – Pratik Lad Jan 11 '23 at 07:23