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.
StampITAsked:
Who is Participating?
 
Deepak ChauhanConnect With a Mentor SQL Server DBACommented:
Change the execution mode from 64 bit to 32 bit in SQL Agent job step.

Edit the job step > Click on Execution Option and check mark use 32 bit run time check box and test.

ssisjobstep.jpg
0
 
StampITAuthor Commented:
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.

Message
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.
0
 
Deepak ChauhanSQL Server DBACommented:
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.
0
 
StampITAuthor Commented:
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 ?
0
 
Deepak ChauhanSQL Server DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.