SSIS newbie: Help with Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005

• Ashish Dutt

  SSC Eights!

  Points: 952

  More actions

  November 1, 2014 at 9:53 pm

  #300707

  This is my first question here and before posting this question I have checked most of the previous questions same as these or related to this one but I'm unable to solve the problem despite applying the fixes suggested by other members. Please, help.

  My scenario: I have put multiple excel sheets (in MS Excel 2007 format) in a single workbook. I have created two User variable with package scope. Excel sheets have the same format. Values for User variable fileName=c:\users\XYZ\Desktop\Dataset\Sheet1.xlsx and User variable filePath=c:\users\XYZ\Desktop\Dataset\Book1.xlsx. Datatype of both variables is String.

  In excel connection manager, the connection string is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +@[User::fileName]+ ";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";" When i evaluate the connection it returns the following expression which is correct Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\XYZ\Desktop\Dataset\Book1.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

  What I want to do

  : I want to read multiple excel sheets within a single workbook and then write them to a SQL table. I have been able to accomplish reading a single excel file and writing it to a SQL table but not multiple excel files.

  Software installed:

  I'm using SQL Server Enterprise edition R2 with BIDS in it.

  Resolution steps that I have followed so far:

  Have set the DelayValidation property in excel connection manager as True

  Have set the Package property to use 32 bit validation as suggested in this forum as I'm using a 32 bit OS

  I am getting the following two errors,

  Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

  Validation error. Data Flow Task: Data Flow Task: Opening a rowset for "Book1$" failed. Check that the object exists in the database.

  Please tell me what am i doing wrong?

  Thank you