2

I have used flat files in csv format, but have yet to have the need to import excel files till now. So I knew there was an excel source and I started with that. After running into issues I started doing the usual Google shuffle. Lots of mentions about 32 -vs- 64 bit drivers.

Ended up here, seeing that I needed the 2016 Data access redistributable.

Tried that but still no success.

When I try to define the excel source and create a new connection manager to my Excel 2016 file, I get this when I try to view the tables

This makes me question that even if i get this working, would I have to install drivers on my production sql server where this package would run? If so, this is a no-go and I need to look at ways of converting these files first to csv.

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
bitshift
  • 6,026
  • 11
  • 44
  • 108

2 Answers2

0

After working for years with SSIS, I can say that best practice is to convert Excel files into csv since reading from Excel using SSIS may leads to many issues.

If you don't have the choice, yes you have to install these drivers on the production server and make sure that you installed the relevant driver(32/64). Or you can install them both by following this answer:

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

SSIS has lots of issues with Excel 2016. I tried lots of stuff but couldn't make SSIS read Excel 2016. You need to first save your file as .xls file i.e. excel 2007-2010. SSIS will be able to read it then. But if possible try to make it csv file as it is much more reliable.

Ritika
  • 104
  • 5