I am facing really strange issue. I am working with a customer for who, we have created a SSIS package as part of some data exchange process.
What this SSIS package does is, it runs using SQL Agent Scheduler at some interval and it looks into some specific folder and parses any file in that folder. Once parsing is done, that file is moved to Archive folder or if file can't be parsed then will be moved to corrupted files folder.
Now, this SSIS package is stored in FILE SYSTEM. And it is using configuration file and we have modified paths of these folders to network folders. On our client site, both these SSIS package and config file are located at SAN. DBA working for this client side has created a SQL Agent task to run this SSIS package which uses this configuration file.
Question is, when I run this task to test, it get completed successfully (I mean job doesn't get fail). But if I look at task execution history, I see messages like,
Code: 0xC002F304 Source: Move File to Archive File System Task Description: An error occurred with the following error message: "Access to the path is denied.". End Error Error: 2012-01-09 09:51:34.48
This SQL Agent is using a proxy account, and DBA confirmed that this proxy account has FULL CONTROL to all of these folders.
If this is helpful to know then, SQL Server instance which runs this SSIS package, all 3 folders, and SSIS package(with config file) are located on 3 different servers. SQL Server DB runs in a compatibility level of 90 (SQL Server 2005).
Any help or suggestion is appreciated.