Problem in bulk insert

Hi All,

I am trying to perform Bulk Insert for an existing folder containing the data files into an existing destination table . I get the following error :

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\PD Data\PDDW_md_costs\Files_to_load" could not be opened. Operating system error code 5(Access is denied.).".

Here Files_to_load is the existing folder that contains the data files. I am not sure why it is saying that i do not have access to it. I have however also tried sharing the folder providing full access to it.

An input i would like to add is that i am performing all these tasks by remote connecting to a server machine that hosts SQL Server 2005 . I also am remote connecting using the administrator id to do these tasks.

If anyone has an idea on why this has occured please do let me know as this would really help solve the problem.

Thanks & regards,

Satya

I am having the same issue. Did anyone ever fiqure this out?

Thanks in advance.

JM

|||Have you tried using the UNC format for the foldername and path?|||

I'm also having this problem. What is UNC format (sorry if that's a stupid question - I'm a newbie)?

I've specified my input file using the connection manager, and selecting the file from the drop down list inside of Integration Services. It makes absolutely no sense to me why it suddenly can't see the file at runtime.

Thanks,

Laurie

|||Also, I should note that the file that I'm trying to access for the bulk insert is on my own local PC, not a remote drive somewhere on the network.|||

UNC path is \\MachineName\filePath. However, I tried this with no luck. Anyone else have any ideas? I have been stuck on this for a week and ordered a book that was no help.

JM

|||

I got past this problem by moving my data file to a shared drive. While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

The fact that I could see the file and link to it easily with no warning from the Connection manager strikes me as inconsistent design. It should give you an error message or something if the file is going to be inaccessible at runtime. Just my 2 cents worth.

|||One more thing - you also need to use UNC format for the file name and path.|||

>While I am running Integration Services on my local PC, SQL 2005 itself actually resides on another server on my network. When the package attempted to run, it was running from that remote server, causing the unable to access file error.

Is that the case? To run the package remotely you woudl need to be invoking it with SQL Agent.

However, even if the package is actually running locally, if the Bulk Insert Task uses a remote SQL Server, then you are asking that remote SQL Server to perform the bulk insert. That SQL Server must have access to the source file.

You make an interesting point about whether we should validate that remote access at design time. However, the SQL Server Bulk Insert operation does not provide us with an option to validate - we have to invoke the operation to do so, which in turn would execute the Bulk Insert operation.

I'm thinking (before coffee, so don't expect too much) that it would be possible to craft a stored proc which could be called by OnValidate on the BulkInsert task that could effectively validate access to the source file.

Donald Farmer

|||

I have used the UNC path name to specify the folder in which to read. I get the same error. Also, I am not working Remotely. I am accessing directly on the server, actually directly on the desktop.

Every, post I have read has pointed to a permissions issue within the SQL service. Where can I find information on adjusting these permissions?

Also, I am logged in as Administrator when I run the package, I have successfully run the package by specifying a specific file name AND only get the Access Denied error when I choose a folder from which to read. Am I doing something wrong?

I too am writing this before coffee, and not sure if my post makes much sense. But I do thank everyone for the help...jm

|||

Donald,

Thanks for clarifying what was going on. I'm still very new to Integration Services.

|||

Hi,

We are having the same issue. We have two SQL Servers running on the same machine. One is SQLDEV-DBS and it is SQL Server 2000. The other is SQLDEV-DBS\SQL2005 and it is SQL Server 2005. We can bulk insert without issue into the SQL Server 2000 instance but when we try to do a bulk insert into the SQL Server 2005 instance we get the message:

Error: 0xC002F304 at Load Stumast, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\sqlprd-dbs\sqlimport\DLSTD4.TXT" could not be opened. Operating system error code 5(Access is denied.).".

Both servers are running under the same domain accounts so this is a real brain teaser.

Anyone have any ideas? Which service do we need to check? There are a lot more under 2005 than 2000.

Thanks for any and all help.

|||Hi! i am having the same porblem but could not find a solution to this ......|||Have you had any luck with this. I still cannot get this to work. Thanks!|||The flatfile connection manager in bulk insert task should point to the file containing source data and not the folder. So, set the path to the file.