SSIS broadly support OLEDB connections rather then ADO Net. when you choose Excel Source component for having Excel sheet as a source in your DFT, it basically creates a OLEDB Connection. To verify this, simply click "Excel Connection" created in Connection Manager window and press F4 or right click and choose Property. in property windo check the "ConnectionString" Property. you will find here the OLEDB provider for Excel sheet source.
We can simply create this connection using our OLEDB connection as well. For doing this, i will create a OLEDB Connection in my connection manager window. At starting i will point this OLEDB Connection to any OLEDB compliant Database. Once the connection created successfully. We will go to the "ConnectionString" Property of our just created OLEDB Connection. Now change here the property for our OLEDB Source.
Here before going into step wise details, we must know that Microsoft has done major change regarding the OLEDB Provider for Excel version 2007 and onwards. Till version 2003 Excel was using different provider, that is "Jet OLEDB 4.0" but for Excel 2007 and later version it requires the OLE DB provider for the Microsoft Office 12.0 Access. This provider installed automatically with the Microsoft office 2007. If this installation is not done, we need to install the provider separately from http://www.microsoft.com/en-us/download/details.aspx?id=23734
Ok, let’s come back to the “ConnectionString” property of our OLEDB Source, Here we will change this property as per our Excel version. For Excel versions earlier than 2007we will write string as given here
Data Source=<you excel sheet location; ex: c:\MySheet.xlsx> ;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=”Excel 12.0;HDR=YES;”
for Excel version 2007 and later will write string as given here
Data Source=<you excel sheet location; ex: c:\MySheet.xlsx> ;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=”Excel 12.0;HDR=YES;”