Sim, você cria uma variável do tipo type. Execute sua consulta e coloque os resultados nessa variável SSIS. Faça o próximo passo um loop ForEach e use o iterador do tipo de conjunto de registros ADO. Você escolherá sua variável de objeto para usá-la. Espero que isso te faça ir.
Você precisa definir variáveis para usar no conjunto de registros e elas estarão disponíveis para as etapas que você colocar dentro desse contêiner de loop.
Se você está apenas usando tabelas SQL, eu recomendo usar um procedimento armazenado, o SSIS pode simplesmente complicar as coisas.
Isso é da memória, então eu posso estar esquecendo uma configuração ou um passo.