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 18.104.22.168
SQL Server 2008 R2 SP3
Thanks in advanced
Solved! Go to Solution.
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.
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
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.
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\
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
but I'm wondering because access and creating the file manually is permitted.
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.
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."