“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'”

• Installing Ssis In Visual Studio 2019
• Ssis In Visual Studio
• Visual Studio 2015 Ssis Package
• How To Use Ssis In Visual Studio 2017

SQL Server 2012 would install into both Visual Studio 2010 and Visual Studio 2012. This was delivered in both physical media installations and downloadable tooling which was rebranded to SQL Server Data Tools- BI Edition, now just SQL Server Data Tools and the components were just licensed via click through agreement. I have installed Visual Studio 2019, Version 16.1.5. I can go to Help, About and see SSDT and SSRS in the installed product boxes. When I look at other windows, I cannot see the ssis toolbox. 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. If you've recently installed Visual Studio 2019 (VS2019), and then installed the SQL Server Reporting Services (SSRS), the SQL Server Analysis Services (SSAS), and/or SQL Server Integration Services (SSIS) extensions, when you open a project you might be wondering where your menus went. How it was For example, if you had a report open in Continue reading 'BI: Wondering why your SSRS, SSAS. SSIS Deploy Task Support protection level EncryptSensitiveWithPassword and EncryptAllWithPassword; Standalone SSIS DevOps Tools. Don't want to depend on Azure DevOps, Visual Studio and SSIS runtime? Please try out the new Standalone SSIS DevOps Tools! Feel free to ask a question in the Q&A page when you have any issues or feedbacks on it.

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.

Installing Ssis In Visual Studio 2019

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

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.

Ssis In Visual Studio

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.

Visual Studio 2015 Ssis Package

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!

Find more interesting topics on JILAXZONE:

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

How To Use Ssis In Visual Studio 2017

Hi, thanks for reading my 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.

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.