SSIS error on access import

▲
4
▼

I have this error from SSIS when i'm trying to import an access 2013 database to a sql server 2014 database
I used Microsoft Office 15.0 Access Database Engine OLE DB Provider For the Source
and SQL Server Native Client 11.0 For the Destination

TITLE: SQL Server Import and Export Wizard

Could not connect source component.

Error 0xc0202009: Source - Amounts [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error 0xc02020e8: Source - Amounts [1]: Opening a rowset for "Amounts" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

The error is for the provider?

I just notice that when i choose another Destination for the data (the same as the Source) it throw a similar error but whith another row index for example "Error 0xc0202009: Source - Amounts [43]: SSIS Error..."

sql
sql-server
ms-access
ssis
asked on Stack Overflow Jul 2, 2014 by Cesar Romeroo • edited May 25, 2020 by Machavity

5 Answers

▲
6
▼

There is import wizard bug, when you select 'copy data from one or more table...', the automatic generated sql have single quote on table name, which cause error message, [Opening a rowset for "Amounts" failed. Check that the object exists in the database.] Click the 'preview' button(next to 'edit mapping' button), you can see the sql.

The solution is remove the single quote around table name by go back, choose 'write a query to specify the data to transfer'. now write your query. then next, then double click the [dbo].[query] change to the table name you want, [dbo][your_table_name]

you have to do one table by one table, i finally figure it out and make it works.

answered on Stack Overflow Nov 17, 2015 by hoogw
▲
3
▼

The error indicates that it is not able to find the Amounts table in your Access file. Ensure that the table is present and that you have not specified any extra spaces in the table name.

Looking at the versions of SQL Server and Access you are using I have a feeling it might have to do with the drivers. Can you make sure you are using -

enter image description here

answered on Stack Overflow Jul 2, 2014 by TMNT2014 • edited Jul 2, 2014 by TMNT2014
▲
0
▼

For anyone searching this nowadays, I got this error because one item in a column exceeded the length set in my database.

answered on Stack Overflow Apr 20, 2017 by Michael Felchlin
▲
0
▼

@hoogw is right. But changing parameter in SQL Server to remove quotes is easy, and the import can be done by pressing Next in the wizard; instead of editing individual queries.

execute this command in SqlServer Management Studio.

SET QUOTED_IDENTIFIER OFF

Then right-click database -> Tasks -> Import Data

answered on Stack Overflow Jan 26, 2018 by SRK - Khurram
▲
0
▼

First, open the Access database that you want to import. On the Database Tools tab, select Users and Permissions, then select Permissions of User and Group:

first steps!!

Finally, for each table, enable all of the permissions:

final step!!

Now, execute the procedure to import the database on SQL Server Management Studio. It works for me; I hope it helps you!

Note: My installation of Access is obviously using Spanish, but I hope these screenshots will still help clarify where to go based on my translations and the iconography.

answered on Stack Overflow May 25, 2020 by tuliohleon • edited May 25, 2020 by Jeremy Caney

User contributions licensed under CC BY-SA 3.0