O SSIS é uma ferramenta muito poderosa. Eu usei-o com sucesso para consolidar dados de milhares de planilhas em um banco de dados, atendendo ao conteúdo variado de folhas e colunas. No entanto, é uma ferramenta de desenvolvimento com uma curva de aprendizado íngreme.
Eu usaria o suplemento Excel do Power Query para o seu desafio. Ele pode combinar dados de vários arquivos em uma única tabela do Excel, por exemplo,