2

I'd like to use sql bulk copy in order to load data from *.xlsx file to the data base. But, I've faced the problem when file size is more than approximately 1mb. When I try to open OleDbConnection I get an error

No error message available, result code: E_FAIL(0x80004005)

Does anyone have an idea about such behavior?

P.S. If file size is less than mentioned above everything works as expected.

string connString = connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0 Xml;";

// Create the connection object 
OleDbConnection oledbConn = new OleDbConnection(connString);
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + WorkSheetName + "$" + DataRange + "]", oledbConn);
OleDbDataReader dr = cmd.ExecuteReader();

string ProfDbBulkCopyConnString = ConfigurationManager.ConnectionStrings["DbBulkCopyConnString"].ToString();
SqlBulkCopy sb = new SqlBulkCopy(ProfDbBulkCopyConnString);
sb.ColumnMappings.Add("Status", "ActionStatus");
sb.ColumnMappings.Add("Process", "ProcessExec");
sb.DestinationTableName = "dba.Execute";
sb.WriteToServer(dr);
managerger
  • 728
  • 1
  • 10
  • 31
  • Maybe the source code you use to do this could help... And read [ask] please before asking your next question. – J. Chomel Oct 31 '16 at 13:40
  • @Chomel Here it is. Hopefully, nothing confusing. – managerger Oct 31 '16 at 13:58
  • Now we get more information! – J. Chomel Oct 31 '16 at 16:37
  • Is there a space in your path string? – Bridge Nov 01 '16 at 12:07
  • @Bridge The string is posted exactly as it is in the code. It seems to be correct. Doesn't it? I incline that the problem connected with driver but I can't figure out exact cause. – managerger Nov 01 '16 at 12:22
  • @managerger I meant the file path value - doesn't it need to be contained in double quotes inside `connString` ? (Side question - why do you assign connString to itself on the first line?) – Bridge Nov 01 '16 at 14:03
  • @Bridge I'm pretty sure that file path doesn't need to be limited with double quotes, so as it is typed above. Oh, sorry it just misprint :-(. – managerger Nov 01 '16 at 14:50

1 Answers1

2

Just a brief description of fixing. For more information I recommend to visit:

Essentially, xlsx format is some kind of zip archive with a bunch of xml files. So, first of all ACEOLEDB provider try to unzip all data directly to memory buffer, but in case of large xlsx file provider unable to unzip all data to memory buffer and it forced to create temp file on the hard drive. If user hasn't permission to the folder Content.MSO on hard drive mentioned problem appears. Path to folder depends on your enviroment. In my case it is C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO (32-bit driver on 64-bit Windosw Server 2008 R2). So, grant access to Content.MSO for user "IIS AppPool\DefaultAppPool" and problem goes away.

Community
  • 1
  • 1
managerger
  • 728
  • 1
  • 10
  • 31
  • Path for me is: C:\Users\myusername\AppData\Local\Microsoft\Windows\Temporary Internet Files. But Content.MSO is not there? Also there is not any option to create this folder? – Himalaya Garg May 16 '18 at 06:48