troubleshooting Question

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

Avatar of Juan Ocasio
Juan OcasioFlag for United States of America asked on
Microsoft OfficeSSIS
6 Comments1 Solution15 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros