Extracting Data From Excel with SSIS

~ Michael Alfes

Working with SSIS packages deployment. Ran into Excel connections problems.

Marius' SQL Server BI Blog

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:

  1. Create a new SSIS project and package.
  2. Right click in the Connection Managers tab and select New Connection…
    SSIS Extracting data from Excel 1
  3. On the Add…

View original post 517 more words

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s