Avatar of StampIT
Flag for United States of America asked on

NOPROVIDER_ 64BIT_ERROR attempting to import Access table to SQL Server

I had this question after viewing SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.

I have a similar issue. I am trying to import a table from an Access 2016 to SQl Server 2008 R2. This is successful if done through Management Studio -- Tasks -- Import Data. However I would like to automate through a Job in SQL Server Agent. Created a package to do this during the manual import by checking "Save SSIS Package". When I try to run this package through a job I encounter the exact same error above. Tried to run this package with dtexec as well but get the same error. Is there a way to do this ? Thanks.
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Deepak Chauhan

8/22/2022 - Mon
Deepak Chauhan

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

I did as suggested. Now I get the following error. I have made sure that STAMP\TCM_SERVICES has read and write permission on \\Kermit\WCHub. Have any idea what might be the issue ? Thanks.

Executed as user: STAMP\TCM_Services. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  9:18:28 AM  Error: 2017-11-03 09:18:30.37     Code: 0xC0202009     Source: Import_tblWCHub Connection manager "SourceConnectionOLEDB"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "Could not use '\\Kermit\WCHub\WCHubTCM_be.accdb'; file already in use.".  End Error  Error: 2017-11-03 09:18:30.37     Code: 0xC020801C     Source: Data Flow Task 1 Source - tblWCHub [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2017-11-03 09:18:30.37     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: component "Source - tblWCHub" (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2017-11-03 09:18:30.37     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2017-11-03 09:18:30.37     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:18:28 AM  Finished: 9:18:30 AM  Elapsed:  2.215 seconds.  The package execution failed.  The step failed.
Deepak Chauhan

Above error is showing ""Could not use '\\Kermit\WCHub\WCHubTCM_be.accdb'; file already in use.".    

Please check if that file already opened somewhere else.

Yes it is opened by another user. It is a back end Access file that multiple users may have open. I can successfully execute the import from Management Studio when other users are using the file. I thought there would be a way to do this through a Job as well. A matter of permissions ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Deepak Chauhan

From permissions standpoint you can check the SQL Agent service account ( by which you are running SQL Agent service ) should have permissions on Assess file and database also because using SSMS you are connecting with your ID ( having all administrative permissions everywhere) but whenever we schedule a job , all operation will be done by SQL Agent service account.