(SQL Server 2017 standard, VS 2019 Pro, MSDT 4.6)
I have a very strange and interesting situation: one task of one SSIS package appears to no longer have access to a local folder path.
The job step is a Script task that counts the number of files in a given path (there should only ever be 8 files), which determines a SUCCESS or FAIL path of the rest of the SSIS package (the package runs only once per day). Up unitl about a month ago, the entire SSIS package functioned as expected. I am not certain what has changed, but the script task can no longer access the path (the path is a compound variable, and the underlying C# code trims the path so that there are no leading or trailing spaces).
I have run the SSIS package, logged into my machine as the same service account that runs my SQL Agent, in Visual Studio and the package performs as expected.
As part of the FAIL path of my SSIS package, I send an email to myself regarding the attempted file count, which includes the path the script task attempted to check, as well as the user context it tried access the path, so I know those parts are fine. The domain account that runs SQL Agent is part of the ADMINISTRATORS group on the server, and has no other access problems in any other part of any other SSIS package on the server (there are over 100). I have this exact same package on a different server, and the script task performs as expected.
I have worked with my server admins, my security admins, and my network admins, to compare and contrast the permissions between the "it works" server and the "doesn't work" server, and no one is able to find any reason why the script task is not working.
I am at my wits end trying to resolve this issue. Although not a show-stopper, as I can manually execute the package in VS to load my data, it does take away from any automation I am attempting to establish. I would love any suggestions on where and what to check, as I am completely out of ideas.
EDIT: I have tried using ProcMon to monitor the path to be checked, for any and all activity. Only 2 entries in the log file pertain to that path:
1. CreateFile - for when the previous task downloads the file from the
source FTP server. The result was SUCCESS, with the following
details:
- Desired Access: Read Data/List Directory, Synchronize
- Disposition: Open
- Options: Directory, Synchronous IO Non-Alert, Open For Backup
- Attributes: n/a
- ShareMode: Read, Write, Delete
- AllocationSize: n/a
- OpenResult: Opened
2. CloseFile - for when the writing of the downloaded files is complete.
* The result was SUCCESS.
Asked by Kulstad
(95 rep)
May 9, 2025, 12:20 PM
Last activity: May 9, 2025, 03:01 PM
Last activity: May 9, 2025, 03:01 PM