How to bulk insert a flat file from remote UNC path to Sql server 2005 using Bulk insert task?

Balahfc
Balahfc used Ask the Experts™
on
I'm getting  an error saying [Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "\\a3021284\share\Imports\SOR91881\InterchangeCntlNbr" could not be opened. Operating system error code 5(Access is denied.).

\\a3021284\share\Imports\SOR91881 is a shared folder and  InterchangeCntlNbr is flat file.

My environment is like this i have a package that creates five flat files in a shared folder with in sql server itself and i bulk insert all the flat files to five different tables. This is working fine because the shared folder is in the server \\ncpctst\imports\SOR91881 (ncpctst - is the sql server name). I created a folder named  "imports"  inside the sql server's  D drive and shared this folder and gave full permission to my domain account and i added my domain account is sql server role as bulk admin, sysadmin, serveradmin . The flat files wil be created in this folder and bulk insert from this folder. This is working fine. But i trying to bulk insert a flat file from a remote system's share folder using VS 2005. This is not working and i get the  Operating system error code 5(Access is denied.) error.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris LuttrellSenior Database Architect

Commented:
What account is the SSIS service set up with?  That is the account that will need read access to the network share.  When you execute SSIS, it runs and makes connections as this account and not you.

Author

Commented:
Hi CGLuttrell,
I trying to debug the Bulk insert task is VS2005 and i set the \\a3021284\share\Imports\SOR91881\InterchangeCntlNbr in the  package variable and assign this variable to that file connection manager's connection string. I attached a screen shot. During debug which user account i have to give read permission for the network share drive so that it will work during debug mode.
1.JPG
Senior Database Architect
Commented:
I am talking about in SQL Server Configuration Manager, SSIS and SQL Server are separate services logging in as a particular account(s).  
Actually it is the SQL Server account that has to have the read access because the Bulk Load operation is at the database service level  and not running as you.  You may need to work with a sysadmin to get the account permission set up if you do not have the right permissions yourself as this is at the OS level and not just in SQL.
Here are some other Answers that deal with the same type problem:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23926307.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21855033.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22458344.html
HTH
SQLServerConfigMgr.png

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial