Generating Filenames Dynamically in SSIS

A file’s name and location are often used to express what’s inside it. Filenames are not required to be meaningful to human beings but they often follow some sort of pattern for categorizing and describing the data inside them. In this way, we can think of the name of a file as being somewhat like metadata. In this article, I’ll focus on a simple example that follows this idea: generating a filename in SQL Server Integration Services (SSIS) that contains the time and date when the file was created. The file creation time is important metadata that other systems can use to make decisions in downstream ETL processes.

In SSIS, the Script Task is good for this sort of thing because a small bit of C# code can add just the kind of needed flexibility and reuse we require. For example, imagine that a file with Employee information will be created from the HR schema. Those bits of metadata can easily be embedded in the filename because they’re somewhat static with respect to the package. However, adding something dynamic like the current date and time to the filename requires some code. To satisfy the requirements in a reusable way, imagine a simple, string-based template that looks like this:

HR_Employees_{yyyyMMdd}T{HHmmss}Z.csv

The text between the curly braces is what we need to parse out to be replaced with the current date and time values. To find the escaped date and time sequences, a regular expression will do nicely. In Figure 1, observe the template string being evaluated in a popular, web-based regular expression tester.

Using a web-based regular expression tool to find escaped date and time sequences.

Figure 1 – Using a web-based regular expression tool to find escaped date and time sequences. Click or tap to see the full-sized image.

Regular expressions are weird but with a bit of study and tools like the one shown in Figure 1, it’s easy to experiment and learn quickly. There are many such regular expression testing tools online as well as a few that can be run natively on your computer. The simple expression tested here is \{\w+\} which looks rather cryptic if you’re unaccustomed to regular expression syntax. However, it’s really quite simple. Reading left to right, the expression means we’re looking for:

  1. A starting curly brace followed by
  2. Any word sequence followed by
  3. An ending curly brace

As you can see in the target string near the bottom of Figure 1, both of the sequences in the template have been found using this regular expression. That means the regular expression will work in the C# code. All that’s needed now is the code that will find those sequences in the template and replace them with their current date and time values.

Before we look at that however, I must drag a new Script Task onto the control flow of my SSIS package. I also need to add two variables to the package that will be used to communicate with the script. Figure 2 shows the control flow with the new Script Task on the design surface, the two new variables that were added and the opened configuration dialog for the Script Task.

Configuring a new Script Task to the SSIS package.

Figure 2 – Configuring a new Script Task to the SSIS package. Click or tap to see the full-sized image.

After dragging a Script Task object from the toolbox onto the control flow, double-clicking it shows the Script Task Editor dialog. To support the invocation of the C# code, two package-level variables called FilenameTemplate and GeneratedFilename were created. You can see them in the variables window near the bottom of Figure 2. Notice that the FilenameTemplate variable has the text with the escaped date and time parts tested earlier. In the Script Task Editor, the FilenameTemplate variable has been added to the ReadOnlyVariables collection and the GeneratedFilename variable has been added to the ReadWriteVariables. That’s important. Failing to add the variables to those collections means they won’t be visible inside the C# code and exceptions will be thrown when trying to use them.

Now we’re ready to write some script code. Clicking the Edit Script button in the Script Task Editor dialog will start a new instance of Visual Studio with the standard scaffolding to support scripting in SSIS. Find the function called Main() and start working there. The first line of code must fetch the contents of the FilenameTemplate variable that was passed in. Here is the line of C# code to do that:

string template = Dts.Variables["FilenameTemplate"].Value.ToString();

With the template in hand, we can convert and save the escaped date and time sequences with the following line of code:

Dts.Variables["GeneratedFilename"].Value = ExpandTemplateDates(template);

Of course, to make that work, we need to implement the ExpandTemplateDates() function, so the following code should be added inside the same class where Main() function is defined.

This method creates the \{\w+\} regular expression tested earlier and uses it to replace the matching sequences in the template parameter. That’s simple to do with .NET’s DateTime class which has a handy ToString() function that can accept the yyyyMMdd and HHmmss formatting strings found in the template. Figure 3 brings all the code together to help you understand.

The script code to find and replace escaped date and time formatting sequences.

Figure 3 – The script code to find and replace escaped date and time formatting sequences. Click or tap to see the full-sized image.

Before closing the C# code editor, it’s a good idea to run the Build command from the menu to make sure there are no syntax errors. To use the new dynamic filename generator, I’ll add one more variable to the package called Filepath. That will be concatenated with the GeneratedFilename to form the full path on disk where the output file from the package will be stored. The connection manager for that file needs to have its ConnectionString property modified at runtime so I’ll use the Expression Builder to do that.

Using the Expression Builder dialog to modify the target ConnectionString.

Figure 4 – Using the Expression Builder dialog to modify the target ConnectionString. Click or tap to see the full-sized image.

From the properties for the connection manager, click the ellipsis (…) button next to the Expressions property and add an expression for the ConnectionString as shown in Figure 4. Once that expression is saved, the full path and name of the file to be saved will be assembled from the Filepath and the GeneratedFilename variables at runtime.

Bringing it all together, Figure 5 shows the results of running the package with a source table, the target flat file bearing the new ConnectionString expression and a Data Flow Task that moves some data from the source to the target. The data flow itself isn’t relevant so it isn’t shown here. What’s important to demonstrate is that the C# code correctly fetched the template variable, processed the regular expression, matched the sequences, replaced them with the date and time values and saved the new filename. The connection manager’s ConnectionString expression also correctly applied the newly generated filename to the path when saving the file to disk.

A test run showing the dynamic filename that was generated and the file on disk with that name.

Figure 5 – A test run showing the dynamic filename that was generated and the file on disk with that name. Click or tap to see the full-sized image.

I marked up the screen shot with a red arrow pointing to the package log output showing the filename that was generated by the C# code when it ran. The blue arrow points to the actual target file on disk, showing that the two match.

There are other ways to do what’s been demonstrated here. However, I find this solution to be both simple and extensible. The example shown here can be easily modified to include many types of dynamic metadata other than dates and times. Moreover, this is a highly reusable pattern given that you need only copy the Script Task into a new SSIS package and set up a couple of package variables to use it anywhere you like. In the next article in this series, I’ll focus on consuming files with dynamically assigned filenames.