0

Inside a DATA Flow task, I have OLEDB source, data conversion task and excel destination.

I could see data moving from OLEDB source to EXCEL through DATA CONVERSION task. I switched on data viewer and I could see data moving.

I replaced the Excel with a Flat File. The flat file is getting loaded with the data.

But if my destination is EXCEL, then I am not able to see data in that excel file. Total count of rows is around 600,000 and my destination excel is 2007(.xlsx) I am running it in 32bit.

Can anyone please help me out? Please I need it.

Thank you so much in advance.

Manoj Soundararajan
  • 371
  • 1
  • 3
  • 13

3 Answers3

0

Excel 2007 row limit is 65,536. I know the source here is Wikipedia, but it is accurate. Source: Wikipedia Excel 2010 is a million something MS Excel Specs. Might be time for an upgrade.

Carl
  • 36
  • 6
  • The asker has 600,00 rows and is using excel 2007. According to your wikipedia link, excel 2007 has 1,048,576 row limit. That should do it. dont you think? http://stackoverflow.com/questions/197762/is-it-possible-to-see-more-than-65536-rows-in-excel-2007 – KrazzyNefarious Feb 27 '15 at 16:23
  • First link was for the 2007 excel, the second link is the the current excel version. – Carl Mar 04 '15 at 01:01
0

In case you haven't already checked, page/scroll down to the end of the spreadsheet to confirm the data hasn't just been appended below rows that previously held data.

Carl's answer is probably the right fit, but thought I'd share this just in case. I had a similar outcome while developing an SSIS package today.

I tried to transfer data to an Excel sheet that previously had data in the first 1400 rows. I deleted the data in the Excel sheet prior to running the package. The package ran to completion (all green) and said it wrote 1400 rows.

Went back to check the file but there was nothing. Made some tweaks to the package and ran it a few more times with the same result.

Upon closer inspection of the destination Excel sheet, I found that the data actually did get over to the Excel sheet but it didn't start until row 1401...even though there was nothing in rows 1-1400. Did some research but found no solutions that would be worth the time. I ended up just exporting the data to a new file.

JTreece
  • 1
  • 2
0

goto;

Redistributable components version Registry key

Excel 2016

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

Excel 2010

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

change TypeGuessRows 8 -> 0

Ramil Aliyev 007
  • 4,437
  • 2
  • 31
  • 47
arsalan
  • 1