0

I have created an SSIS package (using Data Tools on Windows Server 2012) which pulls data from Excel files and load them into a SQL Table. It works fine in the development environment (Data Tools) and also as a SQL job. To Note: Microsoft Office is NOT installed on the Windows Server machine. However, it does have Access Database Engine 2010 installed.

The only hiccup is that my Excel files need to be in xls format (32-bit, I guess). However, I am now faced with a scenario where the Excel files will be in the XLSX format. So, I need to make my SSIS job compatible with these files.

I have done some research on the issue and came up with the following blogs and webpages:

  1. SSIS package fails with error “If 64-bit driver not installed, run in 32-bit mode”
  2. How to install 64-bit Microsoft Database Drivers alongside 32-bit Microsoft Office
  3. Microsoft.ACE.OLEDB.12.0 is not registered

However, I am still confused on how to proceed further regarding my problem.

To simplify, is the solution as follows:

  1. Uninstall Access Database Engine 2010 on Windows Server
  2. Install AccessDatabaseEngine_X64.exe (which is the 64-bit version of Access Database Engine)

Or are there more to it? Any help would be appreciated.

Note: The priority right now for me is to make the SSIS job work with XLSX files (the perfect scenario would be that it works with both Excel file versions)

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • Test, then have a loop to select either XLS or XLSX. – Solar Mike Jun 27 '21 at 05:31
  • I don't understand. Can you elaborate please? It's not a question of selecting either/or...I need it to work with xlsx files. – user3115933 Jun 27 '21 at 06:17
  • Are you saying it needs to work with both files? or are you saying you built it for cls but instead you need it to work with xlsx? – Nick.Mc Jun 27 '21 at 08:16
  • `.xls` and `.xlsx` files are completely different file formats and so need different "loaders" to handle them. `.xls` files are (mostly) described by Microsoft's [Compound File Binary File Format](https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-cfb/53989ce4-7b05-4f8d-829b-d08d6148375b). A description of `.xlsx` is included in [ECMA-376 Office Open XML formats](https://www.ecma-international.org/publications-and-standards/standards/ecma-376/). You will need separate data flows and switch between them based on the file extension. – AlwaysLearning Jun 27 '21 at 09:42
  • @AlwaysLearning Thanks. Any idea which component I will need in the Data Flow Task? Right now, I am using the "Excel" component as my Data Source. – user3115933 Jun 27 '21 at 11:33
  • @Nick.McDermaid I need it to work with xlsx. If need be, create a new package. Right now, when I point my Data Source (in the Dat Flow) to an Excel (xlsx) file, I have the following error: 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. – user3115933 Jun 27 '21 at 11:33
  • 1
    The first thing you need to know is that SSDT is a 32 bit application so needs the 32 bit version installed for this to work. That may or may not be your problem. I suggest you create a new package and follow this step by step https://learn.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver15 The second thing you should know is that excel is about the worst data import/interchange format and you should avoid it if you possibly can – Nick.Mc Jun 27 '21 at 12:25
  • Can you write vb or c# code in a script component? In which case the microsoft.ace.oledb.12.0 oledb driver will read both .xls and .xlsx files. I do it all the time. If you can I can post a code example. – Ciarán Jun 27 '21 at 20:39
  • @Ciarán Thanks. I would highly appreciate if you could post a code example. VB would be fine. – user3115933 Jun 28 '21 at 09:03

1 Answers1

1

Sorry I didn't manage to get around to posting the code. It's been crazy busy. Howwever, I did answer this on another question...

Best /Fastest way to read an Excel Sheet into a DataTable

Ciarán
  • 3,017
  • 1
  • 16
  • 20