SSIS Create New Folders On The Fly When Writing To A Flat File Connection

Question

I have an SSIS package that is meant to write to multiple files, which are stored like

\\server\MainFolder\DynamicField1\DynamicField2\File.csv 

where the subfolders DynamicField1 and DynamicField2 are not necessarily in existence, but are coming from the variable i store for the ConnectionString.

When I run the package from my desktop, I get the message:

[File [2]] Warning: The system cannot find the path specified.

The error goes on to say:

[File [2]] Error: Cannot open the datafile "\\server\folder\abcd\efgh\filename_20190606115901.csv".

That looks like a good file name.

I tried replacing the dynamic folders with a dash so that the file name would look like:

\\server\folder\abcd - efgh - filename_20190606115901.csv

and that actually seemed to work. The problem is, there will be hundreds of files and it would be best if they could be organized in two levels of sub folders.

Does this mean that I cannot create folders on the fly?

Answer 1

You need to use a File System Task and set the Operation to Create Directory. Pass it a variable of the file path without the filename and it should create the directory.

See this blog article for a good example of creating folders from dynamic paths.

Answer 2

@JonathanFite was right, this required the use of a script task.

Additionally writing to the Excel destination required the use a template file and then a File System Task to copy it to the newly created (if needed) directly. I will answer so that there's a complete set of steps in case someone else comes looking and has the same minimal C# skills that I do. I pieced together most of this from various sources, but I never saw an all in one example of what I needed.

To write to a new Excel destination inside folders that may not already exist requires two steps.

1. Create the folder if it does not already exist. (in C# System.IO's CreateDirectory handles this perfectly and will not overwrite one already in existence.)
2. Take a template Excel file and create a copy of it with the name you want in the spot you want with a File System Task. This neatly works all in one step.

Create the folder:

This requires System.IO, so you need to add that to the NameSpaces at the top. So just paste:

using System.IO;

in the NameSpaces Region at the top. Then to create the folder if it doesn't already exist, add the below to the public void Main() area, replacing the name of the variable with whatever one you are passing in as the folder name.

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

The great part of this C# code, is that it will not overwrite the folder if it already exists, which is exactly the behavior I wanted.

Creating the new Excel file:

You need to first create a template file, with the "table" created and defined. So just load the same kind of data to a hard-coded file location. Once you get it to load once, clear out all of the data and save the file in a secure spot. Then add that file location to a new variable. This will be the source variable for the File System Task.

Now all you need to do, is add the file System Task. Set the Destination to the variable you want the real files to go to, and the template file will automatically get copied to the new location and renamed all at once. The task should look something like the below:

It is actually a fairly straightforward process.