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,