Bulk Insert Task Error: Cannot bulk load because the file could not be opened.

SSIS error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\Deployment\Files\temp.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.)."

 

 

Solution:

 

The Bulk Insert task runs by executing sql command (bulk insert) internally from the target sql server to load the file. This means that the SQL Server Agent of the target sql server should have permissions on the file you trying to load. This also means that you need to use UNC(Universal Naming Convention) path instead to specify the file path (if the target server in on different machine)

Also from BOL (see section Usage Considerations - last bullet point)

http://msdn.microsoft.com/en-us/library/ms141239.aspx

* Only members of the sysadmin fixed server role can run a package that contains a Bulk Insert task.

 

 

UNC path syntax:

 

\\ComputerName\SharedDrive

 

You can refer to UNC paths with, e.g., "\\xxx\y", but not "//xxx/y", which gets turned into "c:\xxx\y". 

You can use names like "\\.\a:", but not "\\.\tape0", which gets turned into "c:\tape0".