CODEWITHSUNDEEP
sqlsql-servert-sql
HDB
HDB

Reputation: 291

Bulk insert multiple files inside a folder

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
as
begin
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'

end

Result Set:

1   Access is denied.
2   NULL

Upvotes: 0

Views: 1262

Answers (2)

HDB
HDB

Reputation: 291

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

Upvotes: 0

RoKa
RoKa

Reputation: 160

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.

Upvotes: 2

Related Questions