SQL BULK INSERT using UNC path

user_986847 0
Viewed 1009

I have a developer pc "A". And I have a SQL Server "B".

My SQL Server is a Microsoft SQL Server 2019.

On server B, I have a database with a stored procedure, which bulk loads data from a text file (using the BULK INSERT command).

I have now created an SSIS project on server A, which calls the stored procedure on server B using the "Execute SQL Task". The connection on the task is pointing at the database on server B. I have tried using the OLE DB connection and ADO.

When I place my text file on server B and reference the file like D:\myFolder\myFile.txt, everything works fine.

When I place my text file on server A and references the file like \\\A\myShare\myFile.txt it fails. The error I get is:

[Execute SQL Task] Error: Executing the query "exec BulkInsert '\\A\myShare\myFile.txt'" failed with the following error: "Cannot bulk load because the file "\\A\myShare\myFile.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.

I have tried logging on to server B and via a File Explorer open the file using the above path. It works fine. And yes, I am logged in as the same user on both servers A and B.

I have also tried giving "Everyone" read/write access to the share, but still the same poor result.

I am only interested in knowing how to fix this problem, using the stored procedure call - I do not wish to rebuild its functionality in SSIS (the stored procedure is maintained by an external company and they may change it at their will, but we agree on how I can call it).

According to what I read from Microsoft, it should not be a problem, call BULK INSERT using a UNC path.

etl bulkinsert ssis sql-server sql
1 Answers

According to what I read from Microsoft, it should not be a problem, call BULK INSERT using a UNC path.

This is true, UNC paths are supported by the BULK INSERT command.

Based on the Microsoft documentation, BULK INSERT has three main requirements:

  • The server must have permission to access both the file and the destination database.
  • The server runs the Bulk Insert task. Therefore, any format file that the task uses must be located on the server.
  • The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.

The first requirement means that you should grant the SQL Server Service account to access the UNC path, not the Windows account you are logging in with.

You should refer to the following articles to find the SQL Server service account name:

Besides, you can learn more about SQL Server service accounts and permissions in the following documentation:

Alternative - Mapping network drive

As an alternative, you can try mapping the network drive within SQL Server. You can check the following articles for more information:

user_70312300
Related