February 9, 2009 at 12:16 pm
Hi,
I'm sure this is something simple that I'm just missing, but I'm going to put it out there in hopes that the fog is clearer for someone more advanced. 🙂
I have an ACCESS database that gets put in a specified location on a weekly basis. Because the name of the file changes weekly, I have set up a variable to read the location and add the file name to the end of the upload directory which in turn becomes my dynamic connection string datasource. All of which works fine.
The problem comes in when I try to use that connection manager as a source. It tells me it's "already opened exclusively by another user, or you need permission to view it's data".
The goal is just to read the MS ACCESS database and do a basic datapump of the 5 tables into my SQL Staging tables for the rest of my processes.
If anyone has any ideas, to include an alternative method, I'd greatly appreciate it.
Many thanks,
Sabrina
February 9, 2009 at 6:06 pm
If (for testing purposes only) you use a simpler package which has an explicit name for the Access db, does it work? I would guess not, but it would be good to rule out the get-filename bit as part of the problem.
Is this a secured Access file? That is, do users have to supply username and password to open it?
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 10, 2009 at 7:18 am
Good question and at least it lets me know that I'm heading in the right direction with my troubleshooting. The simple package "as you describe" is 5 dataflows. The source destination set with no variable connectionstring just pointing to the database. For the 5 dataflows the same database, 5 different tables within the database. The destination 5 different staging tables.
If I run the simple package there is no problem. It's like SSIS has no problem with the 'static' connection. But a variable connection that has to delay validation...its balking.
Has anyone used the connectionmanager realeaseconnection function in a script? That was an additional option that I was investigating, but I'm not sure if I'm applying it correctly. A good reference site, if the guru's think this a viable alternative, would be greatly appreciated.
Thanks,
Puzzled:ermm:
February 10, 2009 at 6:30 pm
An alternative approach would be for you to add a 'Rename File' task at the front of your package to rename the Access db to be the simple, static name which you know that your package works with.
Then process the data - and finally revert the name (if necessary).
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply