I have a strange problem when running my SSIS package with an Excel Source and Excel connection manager.
Basically, the package imports an Excel file from a path on the C drive on the server. The package itself is running on the server too. Now when I connect to the server using Remote Desktop and copy the Excel file to the path on the server directly the package runs fine and imports the Excel file. However, when I map from my own desktop to the path on the server and copy the Excel file to the same path I get a
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
When I right click on the Excel file that I copy via remote desktop and via mapped drive I notice under Properties > Security > Groups and User Names the one copied via Remote Desktop has my Windows Logon in it. However, the one via mapped drive does not have my Windows Logon in it.
I suspect this is the issue and is to do with permissions. I need anyone to be able to drop the Excel file via mapped drive for the SSIS package to import the Excel file.
Anyone, know how I can resolve this?