I have created an SSIS package that imports an excel file into a SQL Server table.
The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where the package will be scheduled I get the below error (from a text file I am outputting errors to using SSIS logging).
After researching, the only recommendations I could find was to set the Run64BitRuntime property to false which I did but still no luck. I doubt this is what is causing my error though because the error is not specifying anything regarding 64 bit (as was the case in the articles I found).
I also thought that it might be that the Server does not have the appropriate Excel drivers but I don't think that is the case either because usually the error message would say something about the drivers not being registered.
I currently don't have access to remote into the server. I can only upload the package to a folder and then it is ran by an application so the only error messages I can see are what is in the text error log I have created.
Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
AcquireConnection method call to the connection manager "Envision"
failed with error code 0xC0209303. There may be error messages posted
before this with more information on why the AcquireConnection method
call failed.
"Envision" is the name of my excel connection manager.
I populate the Excel File Path and the Connection String using expressions.
The connection string expression looks like this:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::SourceFilePath] +";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";"
The SSIS Pacakge is executed by a windows username/account. I think it might be a web services account. (BDS_sprtIIS)
Does anyone have any solutions or suggestions as to how to resolve this issue of the package only working on my local machine but not on the actual server where the package will be deployed to?
I found the below answer on another forum, could it be what is causing my issues? They are basically saying that the Excel connection manager tries to access the users temp folder for some reason and if it does not have access to that folder then it will fail:
I noticed too that the Microsoft.JET.OLEDB.4.0 driver will try to read
the temp directory underneath the profile of the logged in user.
.
… We run our SQL Agents using a lower
level domain account and run our SSIS packages using a Proxy Account.
You are correct as Procmon confirmed it for me as well. I gave the
Proxy Account rights to the profile's temp directory (C:\Documents and
Settings\SQLAgentDomainAccount\Local Settings\Temp) and it worked!
I am not using using SQL Server Jobs or Proxy accounts. The package is simply executed by a windows account most likely thru a command line script.
The windows account has access to the file but I am not sure if it has access to it's "TEMP" folder (which I do not ever reference in the package so I don't know why it would need to have access to that folder)…
Best Answer
There were 2 issues that were preventing the package from running on the server. Below are the 2 issues and the solutions I found.
The package is executed by an application that uses the 64-bit DTexec utility by default but the package needs to be run using the 32 bit version of the utility to be able to properly access the Excel file through the Excel connection manager.
I created a "wrapper" SSIS package that uses an Execute Process Task that calls the 32-bit (instead of 64 bit) DTExec utility and passes the command to open the original package.
I also needed to install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable.
Further Reading:
Microsoft.ACE.OLEDB.12.0 is not registered (Stack Overflow)