question

erlokeshsharma08 avatar image
erlokeshsharma08 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
excelssis excelexcel-connection
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nidheesh.r.pillai avatar image nidheesh.r.pillai commented ·
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)
4 Likes 4 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
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.
1 Like 1 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Thanks, this works wonder!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.