@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.
- 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.)
- 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:
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();
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.