Dear Group:
I have created an SSIS package that imports Excel files and it works as expected but I am having an issue and hoping someone can help.
My development machine does not have access to network paths on which the Excel files are stored, so I have them saved in a location my machine can reach. However, the SSIS package is going to run off the server which does have access to these paths.
So I copy the folder to the server and open the Excel.conmgr files with notepad and edit the ConnectionString, specifically the Data Source portion to the network path.
I verified I entered it correctly, as I pasted the path into the RUN command and it opens the file.
However, when I run this via a SQL Job, I am getting the following error: "The system cannot find the path specified. This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document."
I am not sure what is happening. Am I not able to modify the Excel.conmgr files for an updated paths and I need to do it while building the package in Visual Studio? Or is something else going on?
February 19, 2021 at 4:01 pm
I'm a little confused, you say the "server" has access to the path what does that mean?
The account used to execute the package must have access to the files.
Also if this is on the network you must use a UNC path as drive letters won't always exist for a network resource as that is controlled by the account drive mappings.
For instance when I log onto a server I get some drive letters that point to network servers, but when the proxy account running an SSIS package logs onto the network it doesn't get any drive letters. It must use a UNC path such as \\Servername\path\path\TheFile.xlsx
In dev you can copy it local and have the connstring point to C:\TestFiles\myfile.xlsx then when you deploy you change that connection to the UNC path. Also the account running your package (should be a proxy) must have access to that location.
February 19, 2021 at 4:09 pm
First: thank you for the response. To answer your questions:
When I say "the server has access to the path", it is a UNC path that I cannot access from my local PC, but the server on which the SSIS package is placed has access to it.
When I open the Excel.conmgr file, here is the section in question as it exists on the server giving the error. The server has access to that path.
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ad-sqlserver-3t0e.na.ns.net\External Reports\PasswordReport.xls;Extended Properties="Excel 8.0;HDR=YES";" />
</DTS:ObjectData>
OK so when you're logged into the server you can access the file via the run command. which means your account that you log into the server with has access to that file. But when the job runs it uses either a proxy or the agent service account.
However, when I run this via a SQL Job, I am getting the following error: "The system cannot find the path specified. This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document."
What account is running the package? Do you specify a proxy?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply