November 2, 2012 at 9:03 am
So what I thought would be a very easy process is turning into a nightmare. I created a query in an access database (2003) that is on a networked drive. I set up an OLE DB connection using both Jet 4.0 and Microsoft 12.0 Access Database Engine OLE DB using the UNC path. This worked fine on my desktop, but once I tried to run it on my server it would not be able to find any tables or views. If I were to test the connection it would return no errors. The weird part is that I was able to fix the issue by mapping the network drive, even though I am using the UNC path. The problem is that since I have to schedule this package to run everyday it will be using our SSIS Proxy which I can't map the network drive for. I have never had a problem with .xls or any flat file using this server/UNC path combination. I also tried copying a version of the access database and using it locally which didn't work. I finally tried this from our new server which has SQL Server 2012 and I had the same issues. Any help would be extremely appreciated. I have banging my head for over a month trying to figure this out.
Thanks,
Nick
November 9, 2012 at 9:56 am
Well it seems the solution was embarrassingly easy. It turns out that the access database I was connecting was really just a shell with forms and links to another Access DB in the same folder. This other Access DB had all of the data tables. This wasn't an issue when the server is mapped because the user who set up this database didn't use the UNC path. I moved my query to this data table Access DB and re-pointed my SSIS and everything worked perfectly.
Nick
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply