Avatar of Juan Ocasio
Juan Ocasio
Flag for United States of America

asked on 

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

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,

Microsoft OfficeSSIS

Avatar of undefined
Last Comment

8/22/2022 - Mon