Working with SSIS packages deployment. Ran into Excel connections problems.
If seen quite a lot of questions on forums around extracting data from Excel workbooks using SSIS. I therefore decided to write this post to demonstrate how to extract data from and excel workbook. When extracting data from Excel, driver errors are frequently encountered that relate to 32-bit vs 64-bit drivers, e.g.:
Error at Extract data from Excel [Connection manager “Excel Connection Manager”]: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode.
This article will explain what causes these error messages and how to get around them.
Prerequisites:
- SSIS 2012 (although the process is very similar in 2008)
- Microsoft.ACE.OLEDB.12.0 provider (both 32 and 64-bit)
- An Excel 2010 or 2013 file with data on two sheets.
Steps:
- Create a new SSIS project and package.
- Right click in the Connection Managers tab and select New Connection…
- On the Add…
View original post 517 more words
