SSIS Error loading Excel sheet to table - OLEDB error

Question

I'm trying to create a data flow task which brings data from Excel File to SQL table. I'm doing it in SQL Server Data Tools for Visual Studio 2012 as an SSIS package. However, when I run the task I receive below error:

Complete error message:

TITLE: Microsoft Visual Studio

Exception from HRESULT: 0xC02020E8
Error at Import Leads DM Forecast CPPs prep [Excel Source [579]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

Error at Import Leads DM Forecast CPPs prep [Excel Source [579]]: Opening a rowset for "CPPs$" failed. Check that the object exists in the database.

BUTTONS:

OK

some overview: I have .xlsx file and have multiple tabs in it. But i have to read only one sheet where i am using Data access mode: table or view and specifying the Excel Sheet name as CPPs$. (sheetname is CPPs) I get this file once a year. in the excel sheet i have 4 columns and they have filter on each column. column A has mmyyyy data. i am not sure where i am going wrong in loading the excel DATA TO TABLE.

Would you be able help please?

If you are getting the file once a year, why not receive it as CSV? — J Weezy
– J Weezy, Commented Jul 9, 2018 at 15:35
Additionally, what is your destination? Can you provide screenshot of data flow screen? The error seems to point to the destination not existing. — J Weezy
– J Weezy, Commented Jul 9, 2018 at 15:42
@J Weezy -Thank you for the response. I get this from client and i can't request for CSV. Destination is OLEDB Destination. — swathi
– swathi, Commented Jul 9, 2018 at 16:26
What component does the error occur on - is it on the source component? Are you able to open the source and preview the data in the XLS file? — J Weezy
– J Weezy, Commented Jul 9, 2018 at 16:41
Error was on excel source component.When i try to preview the data in excel source editor i get this error message. Exception from HRESULT: 0xC02020E8 Error at Import Leads DM Forecast CPPs prep [Excel Source [592]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error at Import Leads DM Forecast CPPs prep [Excel Source [592]]: Opening a rowset for "CPPs$" failed. Check that the object exists in the database. — swathi
– swathi, Commented Jul 9, 2018 at 16:57

J Weezy · Accepted Answer · 2018-07-09 17:07:39Z

1

I don't typically work with Excel files (CSV instead). My recommended troubleshooting would be to create the connection through the Source Assistant (make sure you choose the correct version of Excel) to rule out the connection manager. Its possible you have bad data in the Excel file (like blank rows or something), so you might want to copy the data from the CPP tab into a new workbook, with one tab only, and then delete all blank rows and columns as a precaution. Then inspect the remaining data field for invalid characters. I hope that helps.

answered Jul 9, 2018 at 17:07

J Weezy

4,0695 gold badges44 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS Error loading Excel sheet to table - OLEDB error

TITLE: Microsoft Visual Studio

OK

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

TITLE: Microsoft Visual Studio

OK

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related