<div>
You are on the right track. The reason is maily, that the installed SQL server may have (only) &nbsp;a basic set of drivers (mostly 32 bit) where MS Office brings additional drivers and even newer supported formats. <br>So if you use a data provider on SQL server, you either haben to work with the available data providers (which is usually enough for most cases) or your need the enhanced data provider installed on the SQL Server machine.<br>A quick solution may be, just to install the office application on SQL Server, so in your case Excel.<br>&nbsp;
</div>


<div>
<blockquote><br>A quick solution may be, just to install the office application on SQL Server, so in your case Excel.&nbsp;</blockquote>You might use Office features with SSIS without the need to Activate it. Activation is mandatory when you use Office interactively but in this scenario should be enough to install Office without the need of Activate it.
</div>


<div>
Thank you both for your suggestions. &nbsp;Can this be done (interacting with the .xlsx file) without installing Office? &nbsp;Unfortunately, we cannot install office on the server, so I'm hoping for a different solution. &nbsp;All I'm doing is importing the data into a DataTable so I can process the file to finally import into SQL Server.<br><br>Thanks!
</div>


<div>
The default SQL import / export functionality uses even local drivers. There is a 32 Bit and 64 bit version an they offer different capabilities due to the installed drivers. The formats are more native, but exporting to excel is even a common task in SQL server. I'm not aware if the lasted SQL server versions have newer drivers, the older ones are limited to xls, but not xlsx.<br>If this fullfills your need, you can use them.<br><br>If you want to have newer formats, you need the MS Access Database Engine...<br><a href="https://www.microsoft.com/en-us/download/details.aspx?id=54920" rel="ugc">https://www.microsoft.com/en-us/download/details.aspx?id=54920</a><br><br>it they wont install then 2010 version<br><a href="https://www.microsoft.com/en-us/download/details.aspx?id=13255" rel="ugc">https://www.microsoft.com/en-us/download/details.aspx?id=13255</a>
</div>


<div>
<div class="content wysiwyg-content fr-view">
Hello all:<br><br>I've created an SSIS package that has a script task in it. &nbsp;It selects a file in a folder and then connects using the following connection string: <br>string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="c:\PathToFile\File.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\"";<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>OleDbConnection cnn = new OleDbConnection(ConStr);<br><br>This works great on my local machine running in Visual Studio. &nbsp;The problem happens with I deploy the package to SQL Server. &nbsp;The package is failing, but doesn't really tell me why. &nbsp;My suspicion is because I don't have Office installed, however I did find the aceoledb.dll in c:\program files(x86)\common files\...<br><br>Does anyone know why the package is failing? &nbsp;Everything runs up until I try to import the file (importing it to a datatable). &nbsp;I have the code in a try/catch, but it doesn't throw an exception (I'm logging to a table during each part of the execution.<br><br>Many thanks for any help you all can provide,<br><br>Juan
</div>
</div>



Hello all:
I've created an SSIS package that has a script task in it.  It selects a file in a folder and then connects using the following connection string:
string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="c:\PathToFile\File.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\"";
                   
OleDbConnection cnn = new OleDbConnection(ConStr);
This works great on my local machine running in Visual Studio.  The problem happens with I deploy the package to SQL Server.  The package is failing, but doesn't really tell me why.  My suspicion is because I don't have Office installed, however I did find the aceoledb.dll in c:\program files(x86)\common files\...
Does anyone know why the package is failing?  Everything runs up until I try to import the file (importing it to a datatable).  I have the code in a try/catch, but it doesn't throw an exception (I'm logging to a table during each part of the execution.
Many thanks for any help you all can provide,
Juan

processing an excel file on a server without MS Office installed using SSIS

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.