asked
Pull data from Excel without Office
Hi Guys This is a question to SSIS enthusiast. We have a server which does not have MS Office installed. Can we pull data from an excel file sitting in this server using SSIS? Thanks
You don't need MS Office Suite to be installed on the server to the do the activity. All you need is the correct Excel Provider as per your file (XLS OR XLSX) and BIDS / SSDT to develop your package.
Sure! Assuming you know that to connect a SSIS package to a MS Office Excel workbook via an Excel connection manager, the data provider within this connection that is used depends on the version of the Excel file format. •For files that have an Excel 2003 or earlier format (XLS), the Excel connection manager, uses the Microsoft Jet OLE DB Provider. This is deprecated now. (Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Myfile.xls;Extended Properties="Excel 8.0;HDR=YES";) •For files that have the Excel 2007 or later format (XLSX), the Excel connection manager requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Myfile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";) Note - This provider is installed automatically with the 2007 MS Office suite. But this is not your case. Hence, you will have to install the provider separately on the SSIS server To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the 2007 Office System Driver: Data Connectivity Components from the link below (
https://www.microsoft.com/en-us/download/details.aspx?id=23734)
Can you please elaborate on "correct excel provider". Server would be serving as the source where file is kept. We are developing ssis package in a different server.
17 People are following this question.
