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

Answer 1

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.

Answer 2

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 -

Answer 3

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