Bulk insert multiple files inside a folderAsk Question


To the wonderful people at Stack who have helped me time and time again... I am trying to run a bulk insert routine for all the files in a folder. This is my approach, but I seem to have hit a roadblock with a message saying "Access is denied in my select statement"

EXEC [dbo].[procReadFolder] 'C:\Users\ABC\Downloads\NYSE_2015'

I have admin access to all the folders and files so unsure of the next step.

See Logic below:

ALTER procedure [dbo].[procReadFolder] @path sysname
set nocount on

declare @dirContent table(
id int identity(1,1),
FileName sysname NULL
declare  @cmd nvarchar(512)
set @cmd = 'DIR /b ' + @path

insert into @dirContent
exec master..xp_cmdshell @cmd

select * from @dirContent

-- Code to Loop through all the records in the file
-- To be written

-- Routine that takes the file name as a parameter and inserts the file
EXEC [dbo].[BulkInsert] 'Path'


Result Set:

1   Access is denied.
2   NULL


You will need to ensure that the account that the SQL Server Service is running as has access to the specific path.

The stored procedure is executed under the security context of the account configured to run the SQL Server Service, and therefore that is the account that will need to be given permissions to the folder on the drive.


I changed the SQL service account to another user account "SQLService" instead of the default NT/MSSQLServer account and it worked

标签: sql-server tsql
© 2014 TuiCode, Inc.