What is the possible solution for this? I force execute the SSIS Job
but the error encountered is
Microsoft jet database engine cannot open the file.''. It is already opened exclusively by another user, or you need permission
What is solution for this I already change the security
This is the flow of the SSIS. The process of this is to update the data of the SQL Server coming from the MDB.
If I'm ever required to use a communal data source like this, especially if the tooling (Excel/Access) takes an lock when someone is just looking at the file, I find it beneficial to copy the file elsewhere for processing.
Before your Update Control Rate data flow, have a File System Task. Define it as a copy with overwrite/clobber/replace from \\server\share\TDMAT TEST\TDMAT.mdb (or whatever the obscured path is) to a local folder that SQL Server Agent/Service Account/SSIS credentialed account has read/write access to. I usually have a folder defined like C:\ssisdata\data_domain\input. So, copy TDMAT.mdb to C:\ssisdata\tdmat\tdmat.mdb.
Then, you have your JET connection managers reference the local file. Away goes your concurrent usage issues. The clever among you might question why we can copy a file that is "locked" but not read it and I can't tell you the why, just that this approach works.