How to load Excel XLSX file using SSIS on Visual Studio 2019

“No tables or views could be loaded.” or “Could not retrieve the table information for the connection manager ‘Excel Connection Manager’.” or “Failed to connect to the source using the connection manager ‘Excel Connection Manager'”

Are you getting any of the above errors when trying to load XLSX file using SSIS on Visual Studio 2019?

You are not alone. Good thing is that, I managed to figure it out and successfully loaded the XLSX into SQL Server Database table using SSIS. I would like to share with you here.

This is my Scenario

I have the following simple scenario, loading an Excel XLSX (.xlsx) file into a SQL Server Database Table using SSIS on Visual Studio 2019.

Though the scenario looks simple, but as it turns out, even using the latest Visual Studio 2019, loading .XLSX file to SQL Server Database table isn’t so straight-forward. As a comparison, if you are loading .XLS file (Excel 97 to 2003), it’s so straight forward.

Issue with loading .XLSX file

The issue is not on the configuration, but more on getting the correct component to be installed to get SSIS acknowledge the XLSX file as XLSX is not something that comes built-in when you install SSIS on even the latest Visual Studio 2019.

The component you need is called Microsoft Access Database Engine 2010 Redistributable.

I know, you must question me, why Microsoft Access? But this is really the correct component to use when you need to load XLSX file via SSIS.

Here’s the link to install Microsoft Access Database Engine 2010 Redistributable.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

One thing to note is that, regardless whether your machine is 32-bit or 64-bit, but when you download it, choose to install the 32-bit option.

This is the setup needed

The method works at the environment and tools I’m using, so if yours are a little bit different, your mileage may vary – regardless, if you need help, just comment out on the comment section down below.

Environment:

This is my environment.

• Windows 10 64-bit

• Excel from Office 365 MSO 64-bit

Tools:

These are the tools I’m using to load .XLSX file into SQL Server Database table.

• Visual Studio 2019 Community Edition
• SQL Server 2019 Express
• SQL Server Management Studio V18.4

The Setup and Steps Required

Using the environment and tools mentioned above, here are the setup and steps required to load XLSX file to SQL Server Database Table via SSIS.

Ensure you have the XLSX file ready, SQL Server Instance, database and table for storing the XLSX file contents are created.

1) Open your browser, download and install “Microsoft Access Database Engine 2010 Redistributable” (I’ll call this as “The component”) from the link given above. Follow the installation wizard (basically just click “Next”).

2) Once the component has been installed, open Visual Studio 2019, and create a new Integration Services Project or open your existing one (if any), then on Control Flow window, drag and drop “Data Flow Task” from SSIS Toolbox, then double click on it.

In case you can’t find Integration Services Project, that means you have not installed SSIS on your Visual Studio 2019 instance. Here’s the SSIS download link that is compatible with Visual Studio 2019: https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects&ssr=false#overview. Follow the installation wizard. Skip this step if you have SSIS installed.

3) On Data Flow window, drag and drop “Excel Source” from SSIS Toolbox, then double click on it.

4) Under Excel Source Editor, on Connection Manager, click on “New” to create new Excel Connection Manager.

5) Under Excel Connection Manager, on Excel File Path, click “Browse”. Locate and choose your XLSX file that you wish to load into SQL Server Database Table. Ensure the Excel version selected is “Microsoft Excel 2007-2010”. Then click “OK”.

6) Back to Excel Source Editor, choose the Data access mode as “Table or View” then under the Name of the Excel Sheet, choose your Excel XLSX worksheet name (on my case the worksheet name is PRODUCT_SHEET, so I’m choosing PRODUCT_SHEET$). Click on “Preview”. If you did everything successfully, you will see the data on the preview.

7) Still on Excel Source Editor, now on the left pane, click on “Columns”, map the External Column accordingly and correctly. Click “OK” once done.

At this step, all setup to load Excel XLSX file has been completed. In case you need to know how to load this XLSX into SQL Server Database Table, continue below, otherwise that’s it.

8) Back on Data Flow window, drag and drop “ODBC Destination”, create/select the Database connection under ODBC connection manager, then under Data Access Mode, choose “Table Name – row by row”, then on Name of the table or the view, select the table that you wish to load the data to accordingly. Click “OK” once done.

9) Then connect Excel Source to ODBC Destination.

That’s it. Now it’s time to test out to see if it works by clicking on the Start button. If you did everything as stated here, you should be able to load Excel XLSX file to SQL Server Database Table via SSIS on Visual Studio 2019.

Let me know your thoughts/difficulties in case you didn’t make it or stuck unto something on the comment section down below, I’ll be gladly help!

---

Do you have anything you want me to cover on my next article? Write them down on the comment section down below.

Alternatively, find more interesting topics on JILAXZONE:

FREE!	Life	Tech
How-To	Positive Cash Flow	Android
Travel	All articles	iOS & iPhone

JILAXZONE – Jon’s Interesting Life & Amazing eXperience ZONE.

---

Hi, thanks for reading my curated article. Since you are here and if you find this article is good and helping you in anyway, help me to spread the words by sharing this article to your family, friends, acquaintances so the benefits do not just stop at you, they will also get the same goodness and benefit from it.

Or if you wish, you can also buy me a coffee:

Thank you!

Live to Share. Share to Live. This blog is my life-long term project, for me to share my experiences and knowledge to the world which hopefully can be fruitful to those who read them and in the end hoping to become my life-long (passive) income.

My apologies. If you see ads appearing on this site and getting annoyed or disturb by them. As much as I want to share everything for free, unfortunately the domain and hosting used to host all these articles are not free. That’s the reason I need the ads running to offset the cost. While I won’t force you to see the ads, but it will be great and helpful if you are willing to turn off the ad-blocker while seeing this site.