“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'”
Dec 18, 2019 The worst thing is that even having found the menu, sometimes the menu only shows two items (Work offline and Debug Progress Reporting). To make it show all the items again, click on the “Control Flow” area and then go back to the menu (it may be necessary to repeat this several times until it works). Apr 23, 2021 The full form of SSIS is SQL Server Integration Services; SSIS tool helps you to merge data from various data stores; Important versions of SQL Server Integration Service are 2005, 2008, 2012, 2014 and 216; Studio Environments, Relevant data integration functions, and Effective implementation speed are some important features of SSIS.
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.
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!
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 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.
Posted June 20, 2020 by Vishwanath Dalvi in Database, SQL Server
A walkthrough and learning how to export & import ISPAC files from SSISDB and Visual Studio. ISPAC file referred to as a deployable output file generated from SQL server integration projects. When we build SSIS project it creates an ISPAC file under the bin folder. Besides, while deploying SSIS projects from Visual studio is equivalent to deploying ISPAC files in the back hood.
Assuming a scenario when we have already deployed SSIS project under the SSISDB catalog. Further, we want to deploy the same project to the other SQL server instance. Either we can deploy it from a Visual Studio, oppositely we can export an ISPAC file from SSISDB and Import the file on another server, as manageable as that.
Here, we have an AdventureWorksStaging project deployed under SSISDB. We want to create ISPAC directly from SSMS so that I can import it on another SQL server instance. Using the same ISPAC file you can re-create SSIS solution if necessitated using Visual Studio.
Figure 1 – AdventureWorksStaging under SSISDB
How To Export ISPAC file from SSISDB in SSMS
1.In SSMS, go to Integration Services Catalogs > expand the folder and navigate to your SSIS project.
2.Right-click on the project >Export.
3.Save the project as “AdventureWorksStaging.ISPAC” file.
Your ISPAC file is ready to be migrated to another SSISB integration catalog on the same or different SQL server instance.
How to Import ISPAC file in Visual Studio
We have had situations where we needed to create a solution from an ISPAC file. To investigate what SSIS package we had deployed. This can also help if you have accidentally deleted the original SSIS project solution and want to re-create it from an ISPAC file.
1. Go to Visual studio File >New > Project.
2. Navigate to Business Intelligence >Integration Services Import Project Wizard.
3. Give Name to your Project & Solution > OK.
4. In Integration Services Import Project Wizard > Next > Select Project deployment file > browse AdventureWorksStaging.ISPAC.
5.Click Next > Import. Your project is successfully imported in Visual Studio.
Following the above steps, we are able to re-create the SSIS project solution from imported ISPAC file.
Figure 3 – Imported ISPAC file as SSIS Project Visual Studio
Download Ssis
Summary & Read More
In nutshell, we have learned how to export and import the ISPAC file in SSISDB and Visual Studio. This really helps to avoid rebuilding the project over and over again. Instead, we can quickly export ISPAC and import on other server instance. We also have the option to re-create the SSIS project using ISPAC file.
Read More and browser through more posts related to SQL Server on Tech-Recipes.
1. How To Create SQL Server Database Project In Visual Studio
2. How To Enable Dark Theme In SQL Server
3. How To Use SET IDENTITY_INSERT In SQL Server
About Vishwanath Dalvi
View more articles by Vishwanath Dalvi
Install Ssis In Visual Studio
The Conversation
How To Open Ssis In Visual Studio 2019
Follow the reactions below and share your own thoughts.