Blob Storage multiplle files

Question

Hello Team,

In a container there are 5 files are present.With the same column name.But the order of the column name are different among all the 5 files.

FolderName :

File1,
File2,
File3,
File4,
File5

When i run query in MS Sql server i am getting the error :
Msg 15813, Level 16, State 1, Line 1
Error handling external file: 'Max errors count reached.'. File/External table name: 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename*.csv'.

Please advise how i can solve this issue

Regards
RK

Answer 1

Hi @Rohit Kulkarni ,

Have you created a database scoped credential created for a shared access signature and created an external data source pointing to an Azure storage account before accessing data in a CSV file?
Please refer to bulk access to data in Azure Blob storage and Configure PolyBase to access external data in Azure Blob Storage which might help.
In addition, it seems there is something wrong with the OPENROWSET statement in your query. Please refer to OPENROWSET Syntax for more details.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

Just for example I am copying the query which i used in this format:

SELECT * FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename*.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') WITH
(
fieldName1 nvarchar(50)
fieldName2 nvarchar(20)
fieldName3 tinyint,
fieldName4 nvarchar(50),
fieldName5 int
) as r

Answer 3

I wanted to write the same like :

SELECT * FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename*.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') with
select * from tableName
) as r

But not able to find the correct syntax.Please advise.

Answer 4

The solution is got fixed after changing the data types for few of the columns.

Thanks

RK

Answer 5

I am having the same issue except I'm using a simple copy data synapse pipeline, so I don't believe I can change data types on any column.