UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing Access Data To A SQL Server Using Import And Export Wizard, Access 2016    
 
   
keen1598
post Dec 19 2017, 02:04 PM
Post#1



Posts: 18
Joined: 5-September 14



Hi All,

I was hoping someone could explain something to me. I'm looking to write a SSIS Package to import data from an Access 2016 accdb file to SQL Server Native 11.0. To do this simply I would run the 64 bit SQL Server Import and Export Wizard to get the data from the desired tables and place them in a specific database on my server. Then to preserve the process I would click the Save SSIS Package checkbox which would save the process as an SSIS Package on the SQL Server. When running this process using SQL Server Import and Export Wizard, regardless of how many people are using my Access DB, it works without issue.

If I try to execute the package from SQL Server Management Studio using an SQL Agent Job, the package fails due to a file in use error.

Does anyone know why this happens or how to correct it?

Thanks,

Michael
Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 05:15 PM
Post#2


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


FIrst, which version of SQL Server was this?

"SQL Server Native Client 11.0." is an ODBC driver that allows you to connect SQL Server instances.

Second, we really can't see what's going on there with your SSIS package, so diagnosing an error tends to be a bit of guess work.

For example, what properties/options did you select to include in the package?
Go to the top of the page
 
keen1598
post Dec 19 2017, 05:42 PM
Post#3



Posts: 18
Joined: 5-September 14



"SQL Server Native Client 11.0." is a correct assumption.

I also set a SQL Authentication using an admin account as the credentials for the SQL Server Connection. The Access DB does not have built in security so those were left blank. You need to use an AD Account to access the database to begin with.

No other properties or options were used so far as I can remember.
Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 06:01 PM
Post#4


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


DOes the error or an error log entry identify "which" file it thinks is in use?
Go to the top of the page
 
keen1598
post Dec 27 2017, 11:30 AM
Post#5



Posts: 18
Joined: 5-September 14



If anyone has the access file open, it reads the access db as in use.
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 11:40 AM
Post#6


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


"If anyone has the access file open, it reads the access db as in use."

Yes. That makes sense. If someone opens the accdb, it is in use.

So, in order to make this process work, you do need to get exclusive control over the accdb. Can you do that?
Go to the top of the page
 
PhilS
post Dec 27 2017, 11:48 AM
Post#7



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
If anyone has the access file open, it reads the access db as in use.

Did you actually verify that this is only the case if the file is opened by anyone else?

I would rather think that this will always happen and it's only a misleading error message that put you on the wrong path.
I guess, the core problem is, the SQL Agent user account does not have (enough) file system permissions to access the Access database file.
Go to the top of the page
 
keen1598
post Dec 27 2017, 12:03 PM
Post#8



Posts: 18
Joined: 5-September 14



Yes I have verified when the Access DB is closed by all users, I can run the SQL Agent Job without issue. The real question is how come the Import Export Wizard can import data when the SQL Agent cannot if users are in the file?
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 12:21 PM
Post#9


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


Okay, then, the error message is accurate. If the accdb is open, the Agent can't import. If the accdb is closed, the Agent can import.

I would point to the same issue as Phil then. Permissions for the SQL Agent.
Go to the top of the page
 
keen1598
post Dec 27 2017, 01:35 PM
Post#10



Posts: 18
Joined: 5-September 14



Permissions where? The SQL Agent has access to the file in the folder, otherwise it wouldn't work when no one has it open, and the database itself does not have any encryption, password protection, or AD security built into it. Need clarity...
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2017, 02:04 PM
Post#11


UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA


The agent runs under the context of a Service Account. I am not an expert here, but I have had problems before with scheduling jobs to run under an appropriate Account.

Attached File  agentaccount.jpg ( 55.52K )Number of downloads: 0


Just one more thing to consider.
Go to the top of the page
 
PhilS
post Jan 2 2018, 06:09 AM
Post#12



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
Permissions where? The SQL Agent has access to the file in the folder, otherwise it wouldn't work when no one has it open[....]

One peculiarity of Access is that any user account supposed to be working with a database does not only need read permissions on the file, but write permissions on the file and folder. This is required to create/maintain the .ldb file.
If any account has only access permissions on the mdb/accdb file itself or no write permissions to the folder, it is able to open the mdb/accdb but cannot use the .ldb, which is required to sync concurrent edits to the database. Then this account/user can open the db file only exclusively! - I suspect that is exactly what you are seeing.
This post has been edited by PhilS: Jan 2 2018, 06:10 AM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 09:21 AM