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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.