Como a tarefa de fonte de dados do ssis Excel sabe em qual linha parar?

Qual é a lógica, não posso dizer e duvido que esteja documentada em algum lugar.

Para "ver" o que a fonte do Excel vai consumir, supondo que você selecione a planilha e não um intervalo nomeado. Abra a planilha no Excel, pressione Ctrl-End uma vez e o Excel navegará até a última célula/coluna onde poderia haver dados. No seu exemplo de 500 linhas, ele poderia realmente ir para a linha 600 porque havia dados nessas células, ou formatação ou algum artefato onde os dados não estão mais presentes, mas algum marcador invisível ainda está na planilha. Destacar as linhas 500-600 e escolher Excluir redefinirá o marcador de final de dados para a linha 500.

Se você descobrir que a fonte do arquivo Excel está lendo muitas linhas em branco além do "fim", eu resolvo o problema identificando o que define uma linha válida de dados (a coluna ID é preenchida, SSN, SalesDate, algo ou algo que define um conjunto mínimo de dados).

Adicione uma coluna derivada: IsInvalidRowserá do tipo Boolean, DT_BOOL, e eu uso uma expressão como ISNULL(MyCol)ou ISNULL(MyCol) || MyCol==""etc, porém você determina o que é uma linha inválida.

Adicione uma divisão condicional e use a condição IsInvalidRowe direcione essas linhas para nada. Conecte o caminho Padrão ao restante do fluxo de dados.

Por que adiciono uma coluna extra?

Depuração/manutenção/capacidade de suporte. Se uma linha inválida estiver indo para o seu destino e você tiver toda essa lógica na Divisão Condicional, não poderá consultar a avaliação da expressão. Ao separá-lo em uma coluna explícita, você pode depurá-lo (geralmente dividindo expressões muito complexas em expressões menores e comparando a saída).