How to read data from an Excel file with SQL Server Integration Services

• As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on dev. machine with Visual Studio (VS).

• Excel Source Properties Setting:

  1. Set the DelayValidation property on the Excel Connection to True
  2. Set the ValidateExternalMetadata property on the Excel Source to False
  3. Set the AccessMode property on the Excel Source to OpenRowSet
  4. Set the OpenRowSet property on the Excel Source to Worksheet name or Table Name (default it reads all the rows)

• There are multiple ways of To Read data form Nth rows

  1. Using the OpenRowset Function, update the OpenRowset value to SheetName$A7:B100 which will allow us to read the data from Column A and Column B starting from the 7th row to the 100th row
  2. Change the Data Access Mode to SQL command Function, update the SQL Command text to select F1, F2 from [SheetName$A7:B100] (select * from [SheetName$], whole sheet)

• Use the Script task to obtain information about available Excel databases (workbook files) and tables (worksheets and named ranges)

Working with Excel Files with the Script Task - SQL Server Integration Services (SSIS) | Microsoft Learn

• Error is The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009

Set the SSIS project in 32-bit mode via Run64BitRuntime to false. In the Project Properties -> Degugging Section -> Run64bitRunTime to False ( close and re-open the project to make the setting valid )

• Error on the Script task Version on the Package validation