Convert Excel File to CSV Then Load to SQL Table Using SSIS

0
votes

I have a very large excel data to load to SQL table. For small data it works. However, for large data (more than 20 million records) I get the following error.

Excel to CSV : There were errors during task validation. Excel to CSV Error: "Excel Source" failed validation and returned validation status and returned validation status "VS_ISBROKEN". Excel to CSV: opening a rowset for "Table1$" failed. check that the object exists in the database. Excel to CSV:Error : SSIS Error code DTS_E_OLEDBERROR. An OLE DB error has occurred .Error code 0x80004005.

2
sql-servercsvssisetl
if you can execute it manullay in bids, then try to set 32bit on your sql job step instead. - SqlKindaGuy
The question I always ask is : where did the excel file come from? can it just be generated as tab delimited to save you the hassle? - Nick.McDermaid

2 Answers

1
votes

My package had no problem. The issue is with the excel file . For small data the extension .xlsx works ( excel 2007) . For the large data i have to convert the data to Excel 97 version which is (.xls). Doing so help load the data

0
votes

Usually when I get this error, something is wrong with the destination table. Check the existence of the table and also match of fields between source and destination.