Archiving and Deleting Empty Files with a Script Task
The requirements to this task are:
• Copy the files to an archive directory.
• Delete files that are less than or equal to 1 KB in size. These files are considered empty.
• Write values to log file.
In this example, the archive directory is on my local device. However, the archive directory can reside anywhere. You will need to establish a connection to the external resource, if you are using one, before you can begin using it. We will be completing each of the requirements above in this article.
1. Prepare the package level variables.
Name Data Type Value
A. vArchive String Provides the location of the archive directory. The location is on my local c:\ drive in this example.
B. c:\Archive\vDate String Populates the date in YYYYMMDD format using the expression:
(DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
C. vFileProcessLog String Filename will be created during execution using the following expression. The expression will embed the date in YYYYMMDDHHMMSS format, within the filename. This log will contain a listing of empty and non empty zip files.
"c:\\Logs\\Document_Extraction_"+ @[User::vDate] +Right("0" + (DT_STR,2,1252) DATEPART("hh",GETDATE()),2)
+ Right("0" + (DT_STR,2,1252) DATEPART("mi",GETDATE()),2)
+ Right("0" + (DT_STR,4,1252) DATEPART("ss",GETDATE()),2) +"_log.txt"
The output of the expression is: c:\Logs\Document_Extraction_20220715163529_log.txt. The filename includes the year, month, day, hour, minute, and second of execution.
D. vSftpInboundFilePath String This is the path to the staging directory. The location is on my local drive for example, C:\Staging\
2. Drag a script task to the canvas and name it appropriately.
3. Use the scripting editor to select C# as the scripting language.
4. Select the ReadOnlyVariables and click “OK”.
Your script editor should resemble the image below.
5. Select the ReadWriteVariables and click “OK”.
Your script editor should now resemble the image below.
6. Click on “Edit Script”. This will open the C# coding editor. Verify you are importing the following namespaces.
#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
7. Declare and bind the script variables.
//Source directory of imported files.
string sourceDirectory = Dts.Variables["User::vSftpInboundFilePath"].Value.ToString();
//Archive directory for imported files.
string destinationDirectory = Dts.Variables["User::vArchive"].Value.ToString();
//Path of log file that will generate at run time.
string logFile = Dts.Variables["User::vFileProcessLog"].Value.ToString();
//Formatted date that will be used as part of the string text entered into the log file.
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
string endFileText = date + ": Test File Processing completed successfully.";
string noFiles = "There are no Test zip files downloaded from SFTP Server.";
//Populate variable with all files in the directory.
var list = Directory.GetFiles(sourceDirectory, "*.zip");
8. Create the function to delete the empty files and record the empty and non-empty files before deletion.
//Delete files that are less than 1 KB in size. This defines and empty zip file.
static void deleteFiles(String source, String logFileName)
{
foreach (string fileName in Directory.GetFiles(source))
{
var fileInfo = new FileInfo(fileName);
string fileNameOnly = Path.GetFileNameWithoutExtension(fileName);
if (fileInfo.Length > 1000)
{
string notEmpty = fileNameOnly + "\r\n";
appendText(logFileName, notEmpty);
}
else
{
string emptyFile = fileNameOnly + ": No files to extract" + "\r\n";
appendText(logFileName, emptyFile);
System.IO.File.Delete(fileName); //Delete empty zip files.
}
}
}
9. Create a function that will write the the recorded content to the file.
//Write to log file
static void appendText(String file, String text)
{
System.IO.File.AppendAllText(file, text);
}
10. Create a function that will copy all of the files to the archive directory from the staging directory. This function will be called before any deletions take place.
static void copyFilesToArchive(string sourceDirectory, string targetDirectory)
{
Directory.CreateDirectory(targetDirectory);
foreach (var file in Directory.GetFiles(sourceDirectory))
File.Copy(file, Path.Combine(targetDirectory, Path.GetFileName(file)));
}
11. Complete the program entry point and encapsulate the code in a try...catch statement.
try
{ //If there are no files populate with approrpriate text.
if (list.Length == 0)
{
appendText(logFile, noFiles);
}
else
//Copy files to archive, delete files less then or equal to 1000 KB , and write out log file contents.
{
copyFilesToArchive(sourceDirectory, destinationDirectory);
deleteFiles(sourceDirectory, logFile);
appendText(logFile, endFileText);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception ex)
{
Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
12. Save your changes to the script and close out of the code editor.
13. Click on the "OK" button to close out of the Script Task Editor.
For reference, the contents of each of the directory's before execution.
14. Execute the task.
The directory contents upon successful execution.
Archive directory contains copies of each of the files.
Log file containing basic information on zip files.
Zip files less than or equal to 1KB in size have been deleted from the staging directory.
All of the zip files have been copied to the archive directory whether or not they contained files. A log file was generated to record which of the files where empty. Empty zip files were deleted and removed from further processing. Each of these tasks could be altered to meet your needs. You have a conceptual working example of how to accomplish each of these goals. In my next post, I will demonstrate how the unpack the zip files while maintaining the folder structure. I will see you there. You will find the code in it's entirety below.
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_d54385b66c94438aa737991ef514d018
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
//Source directory of imported files.
string sourceDirectory = Dts.Variables["User::vSftpInboundFilePath"].Value.ToString();
//Archive directory for imported files.
string destinationDirectory = Dts.Variables["User::vArchive"].Value.ToString();
//Path of log file that will generate at run time.
string logFile = Dts.Variables["User::vFileProcessLog"].Value.ToString();
//Formatted date that will be used as part of the string text entered into the log file.
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
string endFileText = date + ": Test File Processing completed successfully.";
string noFiles = "There are no Test zip files downloaded from SFTP Server.";
//Populate variable with all files in the directory.
var list = Directory.GetFiles(sourceDirectory, "*.zip");
try
{ //If there are no files populate with approrpriate text.
if (list.Length == 0)
{
appendText(logFile, noFiles);
}
else
//Copy files to archive, delete files less then or equal to 1000 KB , and write out log file contents.
{
copyFilesToArchive(sourceDirectory, destinationDirectory);
deleteFiles(sourceDirectory, logFile);
appendText(logFile, endFileText);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception ex)
{
Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
//Delete files that are less than 1 KB in size. This defines and empty zip file.
static void deleteFiles(String source, String logFileName)
{
foreach (string fileName in Directory.GetFiles(source))
{
var fileInfo = new FileInfo(fileName);
string fileNameOnly = Path.GetFileNameWithoutExtension(fileName);
if (fileInfo.Length > 1000)
{
string notEmpty = fileNameOnly + "\r\n";
appendText(logFileName, notEmpty);
}
else
{
string emptyFile = fileNameOnly + ": No files to extract" + "\r\n";
appendText(logFileName, emptyFile);
System.IO.File.Delete(fileName); //Delete empty zip files.
}
}
}
//Write to log file
static void appendText(String file, String text)
{
System.IO.File.AppendAllText(file, text);
}
//Copy all files in staging directory to archive directory.
static void copyFilesToArchive(string sourceDirectory, string targetDirectory)
{
Directory.CreateDirectory(targetDirectory);
foreach (var file in Directory.GetFiles(sourceDirectory))
File.Copy(file, Path.Combine(targetDirectory, Path.GetFileName(file)));
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}