SSIS error on access import

Question

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..."

Tertiary

Does 'amounts' exist on the source server? – Tenebrific

Answer 1

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 -

Ultimately

I just checked the checkbox in the tables list a step before, I didn't write by hand the names so I don't understand where is the error – Tertiary

How did you specify the server name for connecting to the Access database if you used SQL Server Native Client 11.0>? – Ultimately

I used Microsoft Office 15.0 Access Database Engine OLE DB Provider – Tertiary

Please see screenshot above and confirm that this is the driver you are using. – Ultimately

this driver only can recognized ".mdb" format files whereas for new extensions .accdb we must use MICROSOFT Office 15.0 Access Database Engine OLE DB Provider – Lian

Answer 2

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.

Darter

access column(date time) data value run out of range when conversion. These type of error might be some bad value in some row cause failure to copy to destination table. the best way to do it is export each table to excel, then import excel to sql server. I test it with this way, no date time conversion error. – Darter

In my case this was the OLE DB Provider for SQL Server throwing this error, and it is in fact erroneously surrounding table names with quotes. As I am trying to copy 500 tables, this solution will be less than favorable, so if nothing else I know to try to use something less buggy. – Eyebrow

Answer 3

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

Barry

Answer 4

@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

Yim

Answer 5

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:

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

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.

Eisler