SSIS Excel Connection Manager failed to Connect to the Source

0 votes
I have a server that can use the Import Wizard to create and execute an Excel Import task. Using a visual studio 2010 integration services package that I am creating on that server, I am attempting to automate that procedure.

When attempting to design the packaging, the issue arises. I added a connection for Excel and linked it to the local disk's Excel file (the same file I have already successfully imported using the import wizard). When I add an Excel Source to the DataFlow and specify the Excel connection, I only get "No tables or views can be loaded" and receive the following error when I go to the Name Of the Excel Sheet Drop Down.

"Could not retrieve the table information for the connection manager. Failed to connect to the source using the connection manager ..."

I don't know why it is failing, and I can't locate this issue recorded elsewhere. The file is not in use, and the directory is shared with authenticated users.

Any suggestions on how to fix this error? Does running this in 64-bit mode have problems, which I can understand, also apply to development?

It is an Excel 2007 file, I should note.

XLSX and Excel 2007 are selected as the connection.

2019-11-08 If someone tries the answer provided by GavB841 below and it works, please let me know. (I'm no longer employed in this field.)
Oct 28, 2022 in Others by Kithuzzz
• 38,010 points
3,725 views

1 answer to this question.

0 votes

It seems like the 32-bit version of Excel was not installed. Remember that SSDT is a 32-bit IDE. Therefore, when data is access from SSDT the 32-bit data providers are used. When running the package outside of SSDT it runs in 64-bit mode (not always, but mostly) and uses the 64-bit data providers.

Always keep in mind that if you want to run your package in 64-bit (which you should aim for) you will need both the 32-bit data providers (for development in SSDT) as well as the 64-bit data providers (for executing the package in production).

I downloaded the 32-bit access drivers from:

After installation, I could see the worksheets


Source:

answered Oct 28, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused (Connection refused)'.

Hi, @Manjima, what do your system logs say? READ MORE

answered Dec 4, 2020 in Others by Rajiv
• 8,910 points
718 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Excel to IBM DB2 Connection

Try using the IBM DB2 for I ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,420 points
1,236 views
0 votes
0 answers

How to get the connection String from a database

I want to use the database I ...READ MORE

Aug 20, 2022 in Database by Kithuzzz
• 38,010 points
235 views
0 votes
0 answers
0 votes
0 answers
0 votes
1 answer

Cannot connect to Excel SSIS

You need to set run64BitRuntime to False. ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
2,972 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
3,064 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP