SSIS Bulk Insert Fails

Hello again,
My SQL Task uses bulk insert to import a text file.

Cannot bulk load because the file "\...filename.txt" could not be opened. Operating system error code 5(Access is denied.).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Insert to Table

ResultSet is set to None
The file does exist in the specified path and i've displayed the variable to verify it has the correct path.

It fails in Debug mode under my login (domain account).
My login (as well as the SQL Service account) has Full permissions to the folder and file.

Is there anything other than permissions that can cause this error?
Thanks.

Is the file on a path local to the SQL Server? Or is it using a UNC path?

If it's a UNC path, you may also need to create a Share object and grant permissions under the share. Local drive/path permissions do not propagate under UNC access.

1 Like

Why would you use a SQL Task - to execute a BULK INSERT from within SSIS? That makes absolutely no sense - since SSIS has many different source and destination objects specifically designed to extract and import data.

Because you are using an Execute SQL Task - the process is running in SQL Server. Using Windows Authentication as your connection from SSIS - and then attempting a BULK INSERT you are almost certainly running into a double-hop issue.

There is the authentication from your workstation to SQL Server, then authentication from SQL Server to the file system. Even if you setup Kerberos correctly, this is likely to fail because SQL Server cannot initiate authentication.

So back to the original point - why are you doing it this way?

2 Likes

Both of you make good points.
Yes the flat file is using a UNC path.
@robert_volk "Local drive/path permissions do not propagate under UNC access" That's a good point and it explains why it fails.

And sometomes if during debugging or actual running the sql job that runs the ssis and it fails, it locks files. Then next run it fails again. You kjownit is locked when you try to delete the file

OK I'm revisiting this issue. I understand the inefficiency of using Bulk Insert in a SQL Task but I've been able to import flat files this way in other environments. I may be a bit stubborn but I need to better understand why it doesn't work in this case.

This is my scenario:

  • package fails in debug mode on my laptop.
  • Fails whether package references a UNC or a file share
  • Fails even when source file is on my laptop or on sql server
  • the connection to the text file uses a domain account which has Full permissions to the folder and file.
  • ad hoc queries are enabled
  • "Delegation" is not enabled as far as I know.
  • Kerberos has not been reconfigured, altered

I'm not a network guy so I know very little about delegation or Kerberos

With the source text file on my laptop, running the package in debug mode also on my laptop, it fails with [Execute SQL Task] Error: Executing the query "

declare @dynamicBulkInsert varchar(max)

set @d..." failed with the following error: "Cannot bulk load. The file "My file on C drive.......txt" does not exist or you don't have file access rights.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It also fails when I place the file on the C drive of the SQL Server machine.

I don't want to beat a dead horse, just trying to better understand the failures because this technique has worked for me eon other networks.
Thanks for your time.

Sorry if this is mentioned earlier, but have you run a BULK INSERT in SSMS successfully for this? Did that work on the UNC?

In any case, if this continues you'll need to talk to your network admins, have some kind of packet trace maybe. Agree with @jeffw8713 on the double-hop, that PLAGUES me on anything but the simplest transfers. It's going to be impossible for us to troubleshoot this without that kind of feedback.

Maybe you might look into using powershell or python for this. We abandoned SSIS for this type of stuff
Or use .net script within ssis

Admire the tenacity but i think You are spinning your wheels on this for quiet a long time as you keep coming back with these issues in this forum

I just find ssis cludgy imo

Using BULK INSERT means you are executing the code on the server. It doesn't matter where you place the files - you are running a process on your workstation, connecting to the server - and running a process on the server that needs to authenticate to another resource outside of SQL Server.

That will NOT work - even if Kerberos is setup and configured in your environment.

SQL Server will only pass along credentials - it cannot and does not send a new request to the domain controllers to authenticate the login. SQL Server trusts Windows authentication - and trusts that Windows has authenticated the login and therefore does not need to re-authenticate the login.

Using BULK INSERT from within an SSIS task (or Powershell) is asking SQL Server to reauthenticate the login accessing SQL Server for access to the OS - and that won't work.