Bulk insert multiple files inside a folder

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
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

if they helped you so much why you never accepted any of the answers — meda, Oct 28 '15 at 14:01
On a side note, it sounds like you might be able to cut out some work with [this related question](http://stackoverflow.com/questions/11559846) — Bridge, Oct 28 '15 at 14:27
@meda Looks like he has added his own answer though ... roughly the same as mine ... Maybe he doesn't understand how this is supposed to work. — RoKa, Oct 28 '15 at 15:53

RoKa · Answer 1 · 2015-10-28T14:00:13.690

2

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.

edited Oct 28 '15 at 14:00

answered Oct 28 '15 at 13:55

RoKa

160
1
12

It is running on NTService/MSSQLServer ; this is SQL 2014 express on windows 8, I can't find that user object when I try to assign it to the folder – HDB Oct 28 '15 at 14:12
This is because they are "services" rather than actual accounts. I would say that if you are not prepared to reconfigure SQL Server to run as a named account (local or domain), then you will need to grant file system permissions to the per-service SID of these accounts. Details here: https://msdn.microsoft.com/en-us/library/jj219062(v=sql.120).aspx – RoKa Oct 28 '15 at 14:19
1

Alternatively you can setup an xp_cmdshell Proxy account. Which doesn't require an engine reconfiguration. https://www.mssqltips.com/sqlservertip/2143/creating-a-sql-server-proxy-account-to-run-xpcmdshell/ – Brad D Oct 28 '15 at 14:25

score 0 · Answer 2 · answered Oct 28 '15 at 14:24

0

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

answered Oct 28 '15 at 14:24

HDB

251
1
2
12

Bulk insert multiple files inside a folder

2 Answers2