2

I have one .XLSB file (MS excel binary worksheet created using Office 2016) having more than million records. How can I load this using SSIS? I have both SQL server 2014 and SSDT 2012 & SQL Server 2017 SSDT 2017 on two different PC.

The problem is : In SSIS , I'm using excel source , connection manager as excel, the name of the excel sheet is not appearing. Saying, no table or view could be loaded. However, my excel has sheet in it.

Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to the source using the connection manager 'Excel Connection Manager'

EDIT more on error

enter image description here

As per this : Accessing Excel 2007 Binary (.xlsb) via OleDb ACE installed the access (2016) engine. But, still having problem.

What could be the problem ? Any link would help? Thanks.

Hadi
  • 36,233
  • 13
  • 65
  • 124
AskMe
  • 2,495
  • 8
  • 49
  • 102

2 Answers2

0

Install Microsoft.ACE.OLEDB.12.0 32 bit Provider. Then go to project property -> configuration properties -> Debugging -> set Run64BitRuntime = False.

enter image description here

Click on the image to see details about connection property and file extension enter image description here

Khairul Alam
  • 1,266
  • 2
  • 11
  • 31
  • Saying, you cannot install 32 but of MS access DB engine becoz currently have 64 bit office product. Any other clue. Please note that every thing is created using 64 bit, then, why it's required to go back to 32 bit? – AskMe Jul 09 '19 at 12:06
  • For now change the Run64BitRuntime property and build the project. project property -> configuration properties -> Debugging -> set Run64BitRuntime = False. if it does not work then 32 bit version should be installed anyhow. – Khairul Alam Jul 09 '19 at 12:10
  • I did that before. Still, there are problems. It's very bad that Microsoft is not bother about this kind of small issues... Any other clues please? It's too difficult for me go back to 32 bit. Is there any way to save the. xlsb file re save in 64bit format and try?? – AskMe Jul 09 '19 at 12:28
  • Check connection string, for excel source this format is working for me. `Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\..\....xlsx;Extended Properties="EXCEL 12.0 Xml;IMEX=1;HDR=YES"` – Khairul Alam Jul 09 '19 at 12:42
  • Please note that my file is having binary format (I.e. XLSB) . Having same connection sting as yours... I think MS is not prepared for any data format. Very bad. – AskMe Jul 09 '19 at 13:05
  • I think Microsoft.ACE.OLEDB.12.0 32 bit can resolve the issue. Please check the second image i already added. I connect a xlsb and it is working as per early described configuration. – Khairul Alam Jul 10 '19 at 04:31
  • Thanks.In that case, from which MS link you got that? I would like to download this from Microsoft link only. – AskMe Jul 10 '19 at 06:53
  • @AskMe Be aware that unistalling 64-bit version and installing 32-bit may cause other applications to stop working if they were relying on it. Why not installing in Passive mode? – Yahfoufi Jul 10 '19 at 07:14
  • Yes, it will be better to try Passive mode first. – Khairul Alam Jul 10 '19 at 07:30
0

Based on your question you have two problems:

  1. Access Database Engine is not registered error
  2. Reading Xlsb file format using SSIS

Access Database Engine is not registered error

You can refer to the following links for more information on how to solve similar issue:

Reading Xlsb file format using SSIS

You have first to select a normal Excel file from the Excel connection manager (create it manually) then from the ExcelFilePath property change it to the .xlsb path;

Alternatives

  1. Using Interop librairies to read from Excel:

  2. Using Third party librairies to read from Excel:

  3. Using OpenXml librairies:

  4. Convert to csv file

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks Hadi. My problem is I can’t edit registry because don’t have permission to do so and no one from IT will do this for me as they think it’s risky. Is there any alternative ways? Do you think uninstalling 64 bit and reinstalling 32 bit 1st and then may be 64bit will work? Is there any ways to use scripts task or any other task to read .xlsb file and insert data into DB ? Anyways I am going to try the details you have provided and mark as answer as per this. Please let me know if any other clues you have. Thanks again. – AskMe Jul 10 '19 at 01:16
  • @AskMe i added some alternatives – Hadi Jul 10 '19 at 04:26
  • @AskMe make sure that unistalling 64-bit will not affect other applications. Otherwise i think it will work – Hadi Jul 10 '19 at 07:24