cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

bulk insert issue

Hi,

I'm fighting with bulk insert features. The configuration has been followed step by step getting the instructions from the documentation. Unfortunately the System status report the errors:

- Warning Yes Repository BULK INSERT - current status Due to BULK INSERT failures data saving algorithm is switched to slower PREPARED INSERT

- Warning Repository BULK INSERT - startup status Off

DCRUM version 12.4.5.144

SQL Server 2008 R2 SP3

Thanks in advanced

Giuseppe

12 REPLIES 12

raffaele_talari
Inactive

Hi Giuseppe,

could you please attach a screenshot of the Bulk Insert properties you've configured?

Ciao,

Raff

Hi Raffaele,

thanks for the reply.

F:\bulkinsert is a local folder on SQL server, shared with read/write permission to the service user used to start CAS service

bulkinsert.png

Ciao

Giuseppe

Ciao Giuseppe,

a couple of checks:

- can you access the remote dir from the CAS?

- have you already checked the CAS server.log? there you'll find additional info why the Bulk Insert didn't work

Please let me know what you find out.

Ciao,

Raff

Ciao Raffaele,

from the CAS server (logged with proper user) I'm able to access to the remote dir and create folder and files. The server.log seems to hasn't bad events for bulk string, eccept for multiple lines as follow

T RTM16-11-25 10:42:46.356RptGrFinder - bulk inserting a batch of 3192 additional records

Ciao

Giuseppe

Hi Giuseppe,

To check errors related to Bulk Insert in the log files, it is recommended to see in the messages from the boot of the CAS, once the bulk insert is configured only during the boot. That is why you always have to restart the CAS when you make configuration changes for bulk insert.

Regards,

Avner

I found this error on server.log

T HLOG16-11-25 11:19:00.631DB server assumed to be remote
T HLOG16-11-25 11:19:00.647Bulk Inserter folders, for write: \\172.18.0.103\bulkinsert\, for read: F:\bulkinsert\. Table-level locking: true
T HLOG16-11-25 11:19:00.647In order to configure Bulk Inserter you must:
1) set 'bulk.enabled' property in UserProperties table
2) set 'bulk.write' and 'bulk.read' properites in UserProperties table
or 'RtmJob.bulk.write' , 'RtmJob.bulk.read' in RtmProps table
3) specify user which has access to write directory on remote server (log on tab in services)
Current settings are:
bulk enabled :true
write directory :\\172.18.0.103\bulkinsert\
read directory :F:\bulkinsert\
user :Administrator
T HLOG16-11-25 11:19:00.662Adaptive Inserter: refresh interval set to 20 minutes
E HLOG16-11-25 11:19:00.678BulkInsertTest Bulk inserter failed on table BulkInsertTest (F:\bulkinsert\BulkInsertTest_0.txt). Reason :Cannot bulk load because the file "F:\bulkinsert\BulkInsertTest_0.txt" could not be opened. Operating system error code 5(Access is denied.).
T HLOG16-11-25 11:19:00.694Bulk insert test for data source default failed: stored 0 records instead of 667. All inserts will be made by PreparedInserter
T HLOG1

but I'm wondering because access and creating the file manually is permitted.

regards

Giuseppe

You are able to access and create file manually from the remote host (that is, access the folder from CAS)?

Do you have domain users on your environment or only local users? If you do, try to work with an domain users instead of Administrator.

Another try is to give access to everyone for read&write (only temporarily) to confirm that the problem is really related to permissions.

Do not forget to restart the CAS service if changes are made in bulkinsert configuration.

Best regards,

Avner

Ciao Giuseppe,

were you able to solve it?

If so please let us know what was causing this issue.

Thanks and ciao,

Raff

Ciao Raffaele,

I haven't resolved. Unfortunately today I haven't chance to work around the issue.

My last attempt was to give to everyone the full control both to share and folder.

Ciao Giuseppe

Erik_Soderquist
Dynatrace Pro
Dynatrace Pro

That to me looks like it might be on the SQL side rather than the CAS side...

- What permissions does the service account SQL Server is using have to the folder?

- can you log in as the SQL service account and access the folder?

-- Erik

Hello Erik,

thanks for the suggestion, indeed on the folder (F:\bulkinsert\) the permission for the sql service account was missing.
I had setted the permission only for the CAS service user.

Right now both user has read/write permission and all is working as expected.

Ciao
Giuseppe

david_n
Inactive

I think Erik might be on the right track. The user on the SQL side that is accessing the CAS shared folder should have the bulkadmin permission to perform the necessary tasks on the SQL DB. I would suggest checking those permissions to make sure that they are correct.

"On the Microsoft SQL server, ensure that the CAS databaseuser (delta by default) has the bulkadmin server role assigned.

Use the Microsoft SQL Server Management Studio, go to Security > Logins, right-click the delta user, select Properties and go to Server Roles."

https://community.dynatrace.com/community/display/DCRUM124/Configuring+bulk+insert

Thanks,

David Nicholls