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

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

Variables

The package is dependent upon the following variables:

Variable Name	DataType	Expression / Default Value	Remarks
SourceFolder	String	(My source folder path)
FileNamePattern	String	SQLTwins*.txt
CurrentFile	String		Variable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileName	String	SourceFolder + CurrentFile	Fully-Qualified file name to be used by the FileSystem task

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

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.

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.

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.