Error while running bulk insert in SSIS package

[Former Member]

Hi:

I have an error when I am running bulk insert in SSIS package.

I have implemented an SSIS package to update master data directly from R/3, R/3 gives the file in a specified format, I take this and insert all the records into a temporary table and then update mbr table and process the dimension.

This works perfectly well in our development system where both our app server and sql server on the same box. But in QAS, the 2 servers are separate and when I try to run the SSIS package I get the below error.

We have tested all connections and are able to access the path and file from both app server and sql server using the shared folder. Our basis team says that it is a problem with bulk insert task and nothing to do with any authorization.

Has anyone experienced with this sort of problem in multi server environment? Is there another way to load all data from a file into bespoke table without using bulk insert.

Thanks,

Subramania

Error----

---

SSIS package "Package.dtsx" starting.

Error: 0xC002F304 at Insert Data Into Staging Table (Account), Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "
msapbpcapq01\dim\entity.csv" could not be opened. Operating system error code 5(Access is denied.).".

Task failed: Insert Data Into Staging Table (Account)

SSIS package "Package.dtsx" finished: Success.

The program '[2496] Package.dtsx: DTS' has exited with code 0 (0x0).

[Former Member · Accepted solution]

I have had this problem several times, and it was always due to incorrect security.

Mostly we created a special user that was authorized to access the source file/sql server AND authorized to access BPC. We then hardcoded this user in the ssis packages.

Alwin

[Former Member]

Hi Subramania

From your error:

Error: 0xC002F304 at Insert Data Into Staging Table (Account), Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "
msapbpcapq01\dim\entity.csv" could not be opened. Operating system error code 5(Access is denied.).".

Let say, server A is where the file entity.csv is located

Please check the Event Viewer->Security of Server A at the time when the SSIS run, there must be an entry with Logon Failure and find what user was used to access the shared path.

If your both servers are not in a domain, create the user in server A with the same name and password and grant read access to the shared folder.

The other workaround is grant read access to Everybody on the shared folder.

Halomoan

Edited by: Halomoan Zhou on Oct 6, 2008 4:23 AM

[Former Member]

Hi Subramania,

I don't have the answer to your question but I am collecting solutions regarding "automated metadata update" like you created. Is it possible I get a copy op your solution (DTSX and incase also other parts)?

Thx, Johan