SSIS: Accessing Excel 2013 through an OLE DB connection and data processing for different sheet pages

This article mainly summarizes the four aspects of accessing and working with Excel data in SSIS (all Excel files that end with. xlsx)-

1. How to integrate access support and considerations for Microsoft Excel in SSIS.

2. How to connect and access Microsoft Excel files and considerations in SSIS.

3. How to load different Sheet pages of data into the same table.

4. How to load different Sheet pages of data into different tables.

There are a number of ways to connect and work with Microsoft Excel files, including loading processing data using c#.net programming, but this article only considers how to load data from Microsoft Excel files in SSIS.

In previous versions of Microsoft Excel files, it was easy to work with the SQL Server 2008 R2-bids Development tool because the previous version was an Excel file that supported the end of. xls, such as Excel 2003. After the Microsoft Office version was upgraded, they began to adopt a new file type based on OPENXML, which is an Excel file type that ends with. xlsx. However, the tools that include the current SQL Server 2012-SSDT version in BIDS do not have direct driver support for the. xlsx end of Excel files, and therefore need to be manually configured.

Integrate access support for Microsoft Excel in SSIS

Open a few OLE DB drivers seen by an OLE DB Connection in the existing BIDS (SQL Server 2008 R2 and the abbreviation for development tools in previous versions) and SSDT (SQL Server 2012 BI development tool). Why not use the Excel Source component directly? Because the Excel Source component does not support Excel connections and accesses that end with the. xlsx suffix, you need to use a workaround for OLE DB access.