wee11
asked on
SQL Server SSIS and Microsoft Access Processing
Sql Server SSIS packages that interact with Microsoft Access 2016 files have been very inconsistent. I have tried odbc connections as well as using the ACE driver. Some work all the time other access files show locked all the time even when the lock file is not showing. Is there a resource that documents how to work with these issues? Of course it would be nice to know if you really can kill the lock file but I have looked quite a bit for solutions and found nothing that would work with the environment we are working with. These access files are being connected to by other sources that an access front end files. Sometimes even excel is used to pull data. But again I have some that are being connected to in a variety of ways but works in ssis all the time. We are using a unc for the pointer to the access file itself in the package connection. Then we could be pulling from a table in the access file and also pushing data from sql server to a specific table in access which is being worked through the access front end at the same time by multiple users. This is a tough issue no one wants to do this but unfortunately right now there is no alternative. The other factor is some files have passwords and the ones that don't seem to work so the encryption may be part of it. Thanks you
ASKER
So what I discovered is two things have to happen. You have to remove any passwords which encrypts the db. And in this case the service account I am using for the ssis proxy needs to have modified permissions to the file folder. Once these two things are in place the issue appears to have been resolved. If anyone knows how to do this with an access db with a password please let me know. Even when the password is in the connection it still stops the connection.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
If you are trying to control Access via SSIS is not very good idea...better to create Passthrough queries to fetch the necessary data and afterwards manipulate them.