2

While running ssis package from a server i get the following errors.

Data Flow Task:Error: Excel Source failed the pre-execute phase and returned error code 0xC0202009 Data Flow Task :Error: SSIS Error Code DTS_E_OLEDBERROR . An OLE DB error has occurred.Error code:0x80004005 An OLE DB record is available.Source ."(null)" Hresult 0x80004005 Description:"(null)".

To remedy the issue i have loaded the same data in small numbers and no issue .However,if i have excel file with 25000 rows the ssis package fails.Since i am working with larger data , i need a better solution.

Hadi
  • 36,233
  • 13
  • 65
  • 124
MJ8
  • 185
  • 4
  • 19
  • What can go wrong at pre execution phase? – MJ8 Dec 27 '17 at 18:24
  • 1
    Excel file is picky, try to save the same spreadsheet as `Comma Separated file(csv)`, and load it using `Flat file source` – LONG Dec 27 '17 at 18:28
  • The issue with flat file is if my column data has " " in the middle it will abort the process. – MJ8 Dec 27 '17 at 18:41

1 Answers1

2

I faced a similar issue, it was solved using this workaround:

First try to import the excel file to sql using SQL server Import and Export Wizard. if it is done successfully. Just choose to save the package to a file system (select this option in the wizard). Open it and edit the package to add other transformation option.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • @MJ8 there is another workaround is to import the excel file to a datatable in a script task. then store it in a ssis variable of type object. Then use this variable as a source (using a script component in a dataflow task). You can follow this link to for more on importing excel to datatable https://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider – Hadi Dec 27 '17 at 18:55