SSIS import Excel - something strange is going on

Question

Hi, I am importing and .xlsx and the SSIS package does not fail, there are no error messages what so ever . But it is only loading a portion of the total file ..
There are over 1,000 records in the file, but it only loads 964 .. Again no failure. .. I have tried a number of times and ways and always stops at 964 . If you copy the content of the .xlsx into a new excel and save it , it then loads the full file. Any ideas on how to fix this issue ? thanks

Answer 1

SSIS is not currently supported here on QnA. They're actively answering questions in dedicated forums here.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlintegrationservices

--please don't forget to Accept as answer if the reply is helpful--

---

Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows Server] Datacenter Management

Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

Answer 2

Good day @Richards Jr, Robert H HHHH ,

If you copy the content of the .xlsx into a new excel and save it , it then loads the full file.

According to this and without any more information, I would guess that the file is corrupted or (bigger chance) that in row 965 you have some hidden characters which lead to this issue. These problematic character(s) probably where not copied to the new file but are readable to the SSIS, for example optional of NULL Character.

I have a full explanation about the NULL character and the issues but this post I wrote in Hebrew and never translated it yet, so if you do not speak Hebrew, the Google can help you learn more.

1) What is the issue which using the new file? Why do you need to use the original file if you found a workaround?

2) can you upload the original file and the file which succeeded, so people can examine it?

Note! I am not sure who will take the chance to open a file from the forum, but it might help someone which have the time.