#0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code


One of the main reasons I value the interactions with the SQL Server community on various forums is because it often inspires me to explore alternate paths to doing the same task.

With SSIS, there are multiple patterns to achieve a particular outcome – some more prevalent than others. A task that we need to do often in SSIS is to perform file manipulations (e.g. rename or delete files after a data ingest is complete, etc). This is typically achieved by using a script task – but using a script task involves writing code. Therefore, a question that came up was:

Can we perform file operations (move, rename, delete or any other operations) without writing a script or a line of code?

The answer is that this is certainly do-able. In fact, some of my previous posts have used the same mechanism that I am proposing today.

The Solution

Assume that I have a set of files in a folder (following the pattern – SQLTwins*.txt) and I wanted to delete them. The Control Flow is quite procedural:

  • A ForEach Iterator is used to iterate through files in the directory
    • The iterator is completely configurable – allowing the user to specify the folder name and the file name pattern
  • A FileSystem task is used to perform the actual file operation
Control Flow of a package that manipulates files without a script or code!

Now, allow me to walk-you through the package configuration:

Variables

The package is dependent upon the following variables:

Variable NameDataTypeExpression / Default ValueRemarks
SourceFolderString(My source folder path)
FileNamePatternStringSQLTwins*.txt
CurrentFileStringVariable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileNameStringSourceFolder + CurrentFileFully-Qualified file name to be used by the FileSystem task
List of User Variables on the SSIS pacakge

ForEach Iterator

The configuration of the Foreach Iterator is quite simple:

  • Collection
    • The “Descriptions” and “FileSpec” expressions are set with the user variables – “SourceFolder” and “FileNamePattern” respectively
  • Variable Mappings
    • This allows the package to capture the output of the iterator
    • The variable “CurrentFile” will be used to capture the current file name
“Collection” tab of the ForEach Iterator showing “Descriptions” and “FileSpec” expressions set with the user variables – “SourceFolder” and “FileNamePattern” respectively
Variable Mappings showing the output of the ForEach Iterator setting the “CurrentFile” variable

File System Task

The configuration of the FileSystem task is even simpler! Other than the Name, the only configuration I did was to set the “Operation” and the “SourceVariable” variables.

Screengrab showing the configuration of the File System task

That’s it! We are all set to give the package a spin and did not write a single line of code!

When we run the package, we can see right away that the files have been deleted.

Prior to execution, we can see that the files are still present.
Once the package is executed, the files are deleted!

The intention of the post was to demonstrate that with Microsoft SQL Server and related services, there are tools and components available which allow one to get started extremely quickly. If you have never worked with SSIS before, do explore the components available in the SSIS toolbox before getting into some serious scripting!

Further Reading

  • Adding date and time to a file name after processing [Blog Link]
  • Moving and Renaming a File [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.