SSIS cria novas pastas em tempo real ao gravar em uma conexão de arquivo simples

Você precisa usar uma Tarefa do Sistema de Arquivos e definir a Operação para Criar Diretório . Passe uma variável do caminho do arquivo sem o nome do arquivo e ele deve criar o diretório.

Consulte este artigo do blog para obter um bom exemplo de criação de pastas a partir de caminhos dinâmicos.

@JonathanFite estava certo, isso exigia o uso de uma tarefa de script.

Além disso, a gravação no destino do Excel exigia o uso de um arquivo de modelo e, em seguida, uma tarefa do sistema de arquivos para copiá-lo diretamente para o recém-criado (se necessário). Vou responder para que haja um conjunto completo de etapas caso alguém venha procurar e tenha as mesmas habilidades mínimas de C# que eu. Eu juntei a maior parte disso de várias fontes, mas nunca vi um exemplo completo do que eu precisava.

Para gravar em um novo destino do Excel dentro de pastas que talvez ainda não existam, são necessárias duas etapas.

1. Crie a pasta se ela ainda não existir. (no C# System.IO, o CreateDirectory lida com isso perfeitamente e não substituirá um já existente.)
2. Pegue um arquivo modelo do Excel e crie uma cópia dele com o nome desejado no local desejado com uma Tarefa do Sistema de Arquivos. Isso funciona perfeitamente em uma única etapa.

Crie a pasta:

Isso requer System.IO, então você precisa adicioná-lo aos NameSpaces na parte superior. Então é só colar:

using System.IO;

na região NameSpaces na parte superior. Em seguida, para criar a pasta, caso ela ainda não exista, adicione o abaixo na public void Main() área, substituindo o nome da variável pelo que você está passando como nome da pasta.

            // TODO: Add your code here
            string Path = Dts.Variables["User::FolderName"].Value.ToString();
            Directory.CreateDirectory(Path);
            Dts.TaskResult = (int)ScriptResults.Success;

A grande parte deste código C#, é que ele não irá sobrescrever a pasta se ela já existir, que é exatamente o comportamento que eu queria.

Criando o novo arquivo Excel:

Você precisa primeiro criar um arquivo de modelo, com a "tabela" criada e definida. Então, basta carregar o mesmo tipo de dados em um local de arquivo codificado. Depois de carregar uma vez, limpe todos os dados e salve o arquivo em um local seguro. Em seguida, adicione esse local de arquivo a uma nova variável. Esta será a variável de origem para a Tarefa do Sistema de Arquivos.

Agora tudo que você precisa fazer é adicionar o arquivo System Task. Defina o Destino para a variável para a qual você deseja que os arquivos reais sejam, e o arquivo de modelo será copiado automaticamente para o novo local e renomeado de uma só vez. A tarefa deve ser algo como o abaixo:

Na verdade, é um processo bastante simples.