/// <summary>
/// Apply all values received in parameters collection to ReturnValue collection
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
// Iterate through all keys in incoming parameter collection:
foreach (var key in parameters.GetKeys())
{
// Retrieve specified key value, processing date macros:
var value = parameters.GetString(key, null, dateTimeNow);
// Now process any nested macros in resulting value string - regex will capture argument-named macros,
// to allow keys passed as parameters to this adapter (including the keys of return values output by
// previous adapters in batch) to have their values updated with the value of other keys in collection
// (again including return values output by previous steps). For example, if a previous adapter in this
// batch output a return value of "@FileID"/57, placing the parameter "Command"/"echo <@@FileID>" in
// the collection for THIS adapter will result in value "Command"/"echo 57" being placed in the return
// value collection (and then placed into input parameter collections of subsequent steps):
var valuemacromatches = TaskUtilities.General.REGEX_NESTEDPARM_MACRO
.Matches(value)
.Cast <Match>()
// Flatten match collection into name/value pair and select unique values only:
.Select(match => new { Name = match.Groups["name"].Value, Value = match.Value })
.Distinct();
foreach (var match in valuemacromatches)
{
// Retrieve parameter matching the "name" portion of the macro - processing date/time macros
// again - and replace all instances of the specified macro with the string retrieved:
value = value.Replace(match.Value, parameters.GetString(match.Name, null, dateTimeNow));
}
// Add final value to return value collection:
result.AddReturnValue(key, value);
}
result.Success = true;
}
catch (Exception ex)
{
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Check whether specified file(s) exist and set result accordingly
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
string sourceFolder = parameters.GetFolder("SourceFolder", dateTimeNow);
string filenameFilter = parameters.GetString("FilenameFilter", null, dateTimeNow);
if (string.IsNullOrEmpty(sourceFolder) || string.IsNullOrEmpty(filenameFilter))
{
throw new ArgumentException("Missing SourceFolder and/or FilenameFilter");
}
var filenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("FilenameRegex"), RegexOptions.IgnoreCase);
bool recurseFolders = parameters.GetBool("RecurseFolders");
// If custom regex not specified, create one from file filter (this check is performed to avoid false-positives on 8.3 version of filenames):
if (filenameRegex == null)
{
filenameRegex = TaskUtilities.General.RegexFromFileFilter(filenameFilter);
}
#endregion
// Set successful result if any files found in specified folder matching filter and regex:
result.Success = Directory.EnumerateFiles(sourceFolder, filenameFilter, recurseFolders ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
.Select(Path.GetFileName)
.Where(fileName => filenameRegex.IsMatch(fileName))
.Any();
}
catch (Exception ex)
{
logger.LogError(ex, "File checking failed");
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Sleep for a specified number of milliseconds
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
try
{
// Read and validate millisecond delay value:
var millisecondsDelay = parameters.Get <int>("MillisecondsDelay", int.TryParse) ?? 0;
if (millisecondsDelay <= 0)
{
throw new ArgumentException("Missing or invalid MillisecondsDelay");
}
// Await specified delay, and return success:
await Task.Delay(millisecondsDelay);
result.Success = true;
}
catch (Exception ex)
{
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Use the specified database connection string and stored procedure to export data to files (optionally
/// retrieving a queue of files to be exported, performing multiple exports in order)
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
string connectionString = config.GetConnectionString(parameters.GetString("ConnectionString"));
exportProcedureName = parameters.GetString("ExportProcedureName");
string exportFolder = parameters.GetFolder("ExportFolder", dateTimeNow);
if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(exportProcedureName) || string.IsNullOrEmpty(exportFolder))
{
throw new ArgumentException("Missing or invalid ConnectionString, ExportProcedureName and/or ExportFolder");
}
// Create destination folder, if it doesn't already exist:
else if (!Directory.Exists(exportFolder))
{
Directory.CreateDirectory(exportFolder);
}
// Check for a queue monitoring procedure - if found, adapter will call specified procedure to retrieve
// a listing of files to be exported as part of this operation:
string queueProcedureName = parameters.GetString("QueueProcedureName");
var queueProcedureTimeout = parameters.Get <int>("QueueProcedureTimeout", int.TryParse);
// Retrieve other optional parameters:
exportProcedureTimeout = parameters.Get <int>("ExportProcedureTimeout", int.TryParse);
exportPGPPublicKeyRing = parameters.GetString("ExportPGPPublicKeyRing");
exportPGPUserID = parameters.GetString("ExportPGPUserID");
exportPGPRawFormat = parameters.GetBool("ExportPGPRawFormat");
defaultNulls = parameters.GetBool("DefaultNulls");
suppressIfEmpty = parameters.GetBool("SuppressIfEmpty");
ignoreUnexpectedTables = parameters.GetBool("IgnoreUnexpectedTables");
suppressHeaders = parameters.GetBool("SuppressHeaders");
qualifyStrings = parameters.GetBool("QualifyStrings");
delimiter = parameters.GetString("Delimiter");
if (string.IsNullOrEmpty(delimiter))
{
delimiter = ",";
}
bool haltOnExportError = parameters.GetBool("HaltOnExportError");
// Retrieve default destination filename (if present, will override database-provided value):
string exportFilename = parameters.GetString("ExportFilename", null, dateTimeNow);
// Add any parameters to be applied to SQL statements to dictionary:
var atparms = parameters.GetKeys().Where(parmname => parmname.StartsWith("@"));
foreach (var atparm in atparms)
{
sqlParameters[atparm] = parameters.GetString(atparm, null, dateTimeNow);
}
// Finally, read explicit column data from configuration root, if available (if section exists
// and has content, failure to build column configuration will halt processing; if section does
// not exist, we will proceed with default/dynamic data output):
var outputColumnConfig = parameters.Configuration.GetSection("OutputColumns");
if (outputColumnConfig.Exists())
{
outputColumnListSet = outputColumnConfig.Get <OutputColumnTemplateListSet>().EnsureValid();
}
#endregion
// Open database connection
await using (var cnn = new SqlConnection(connectionString))
{
await cnn.OpenAsync();
// Capture console messages into StringBuilder:
var consoleOutput = new StringBuilder();
cnn.InfoMessage += (object obj, SqlInfoMessageEventArgs e) => { consoleOutput.AppendLine(e.Message); };
// Build queue of files to be exported; if no queue check procedure configured, export single default only:
var QueuedFiles = new Queue <QueuedFile>();
if (string.IsNullOrEmpty(queueProcedureName))
{
QueuedFiles.Enqueue(new QueuedFile());
haltOnExportError = true; // Since we are only exporting single file, ensure exceptions are passed out
}
else
{
#region Execute specified procedure to retrieve pending files for export
using var queueScope = logger.BeginScope(new Dictionary <string, object>()
{
["QueueCheckProcedure"] = queueProcedureName
});
try
{
using var cmd = new SqlCommand(queueProcedureName, cnn)
{
CommandType = CommandType.StoredProcedure
};
if (queueProcedureTimeout > 0)
{
cmd.CommandTimeout = (int)queueProcedureTimeout;
}
DeriveAndBindParameters(cmd);
#region Execute procedure and read results into queue
await using (var dr = await cmd.ExecuteReaderAsync())
{
while (await dr.ReadAsync())
{
if (!await dr.IsDBNullAsync(0)) // First column (FileID) must be non-NULL
{
QueuedFiles.Enqueue(new QueuedFile
{
FileID = await dr.GetFieldValueAsync <int>(0),
// Only read second column (optional subfolder) if present in dataset
Subfolder = dr.VisibleFieldCount > 1 ?
(await dr.IsDBNullAsync(1) ? null : await dr.GetFieldValueAsync <string>(1)) : null
});
}
else
{
logger.LogWarning("Discarded row from queue dataset (null FileID)");
}
}
}
#endregion
var returnValue = cmd.Parameters["@RETURN_VALUE"].Value as int?;
if (returnValue != 0)
{
throw new Exception($"Invalid return value ({returnValue})");
}
else if (QueuedFiles.Count > 0)
{
logger.LogDebug($"{QueuedFiles.Count} files ready to export");
}
}
catch (Exception ex)
{
// Log error here (to take advantage of logger scope context) and add exception to result collection,
// then throw custom exception to indicate to outer try block that it can just exit without re-logging
logger.LogError(ex, "Queue check failed");
result.AddException(new Exception($"Queue check procedure {queueProcedureName} failed", ex));
throw new TaskExitException();
}
finally
{
// If queue check procedure produced console output, log now:
if (consoleOutput.Length > 0)
{
logger.LogDebug($"Console output follows:\n{consoleOutput}");
consoleOutput.Clear();
}
}
#endregion
}
#region Process all files in queue
while (QueuedFiles.TryDequeue(out var queuedFile))
{
using var exportFileScope = logger.BeginScope(new Dictionary <string, object>()
{
["FileID"] = queuedFile.FileID,
["ExportProcedure"] = exportProcedureName
});
// If queue entry's FileID is not null, override value in parameter collection:
if (queuedFile.FileID != null)
{
sqlParameters["@FileID"] = queuedFile.FileID.ToString();
}
try
{
// Pass processing off to utility function, receiving any output parameters from
// export procedure and merging into overall task return value set:
result.MergeReturnValues(await ExportFile(cnn, string.IsNullOrEmpty(queuedFile.Subfolder) ? exportFolder : TaskUtilities.General.PathCombine(exportFolder, queuedFile.Subfolder), exportFilename));
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
// Log error here (to take advantage of logger scope context) and add exception to result collection:
logger.LogError(ex, "Error exporting file");
result.AddException(new Exception($"Error exporting file{(queuedFile.FileID == null ? string.Empty : $" (FileID {queuedFile.FileID}")}", ex));
// If we are halting process for individual export errors, throw custom exception to indicate
// to outer try block that it can just exit without re-logging:
if (haltOnExportError)
{
throw new TaskExitException();
}
}
finally
{
// If export procedure produced console output, log now:
if (consoleOutput.Length > 0)
{
logger.LogDebug($"Console output follows:\n{consoleOutput}");
consoleOutput.Clear();
}
}
}
/// <summary>
/// Construct command line for the specified executable and arguments, execute and validate results
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var consoleOutput = new StringBuilder();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
// Retrieve and validate required parameters
string executable = parameters.GetString("Executable");
if (string.IsNullOrEmpty(executable))
{
throw new ArgumentException("Missing Executable name");
}
// Retrieve optional parameters
string workingFolder = parameters.GetFolder("WorkingFolder", dateTimeNow);
string returnValueRegex = parameters.GetString("ReturnValueRegex");
#endregion
#region Create and execute command line process
try
{
using (var process = new Process())
{
process.StartInfo.FileName = "cmd.exe";
process.StartInfo.WorkingDirectory = string.IsNullOrEmpty(workingFolder) ? Directory.GetCurrentDirectory() : workingFolder;
// Construct command string, starting with executable name:
var commandstring = new StringBuilder(executable);
#region Add additional command line arguments from parameter collection
var argumentkeys = parameters.GetKeys()
// Regex supports up to 10 arguments, named as "argument0" through "argument9":
.Where(argumentkey => argumentKeyRegex.IsMatch(argumentkey))
.OrderBy(argumentkey => argumentkey);
foreach (string argumentkey in argumentkeys)
{
// First retrieve the specific argument by key name, processing date macros:
string argumentvalue = parameters.GetString(argumentkey, null, dateTimeNow);
// Now process any nested macros in resulting argument value string - regex will capture argument-named macros,
// to allow values passed as parameters to this adapter (including return values output by previous adapters
// in batch) to be placed directly into command string. For example if a previous adapter in this batch output
// return value "@FileID"/57, placing the parameter "Argument0"/"-FileID=<@@FileID>" in the collection for
// THIS adapter will result in value "-FileID=57" being placed in the command line to be executed:
var argumentvaluemacromatches = TaskUtilities.General.REGEX_NESTEDPARM_MACRO
.Matches(argumentvalue)
.Cast <Match>()
// Flatten match collection into name/value pair and select unique values only:
.Select(match => new { Name = match.Groups["name"].Value, Value = match.Value })
.Distinct();
foreach (var match in argumentvaluemacromatches)
{
// Retrieve parameter matching the "name" portion of the macro - processing date/time macros
// again - and replace all instances of the specified macro with the string retrieved:
argumentvalue = argumentvalue.Replace(match.Value, parameters.GetString(match.Name, null, dateTimeNow));
}
// Add final argument value to command line:
commandstring.Append($" {argumentvalue}");
}
#endregion
// Construct cmd.exe arguments starting with /c (to trigger process exit once execution of command is
// complete), adding command string in quotes (escaping any quotes within string itself), and ending by
// redirecting stderr to stdout (so all console output will be read in chronological order):
var finalcommandstring = commandstring.ToString();
process.StartInfo.Arguments = $"/c \"{Regex.Replace(finalcommandstring, "\\\\?\"", "\\\"")}\" 2>&1";
logger.LogDebug($"Executing [{finalcommandstring}]");
// Don't execute command inside shell (directly execute cmd.exe process), capture console output to streams:
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.RedirectStandardError = true;
// Add event handler for console output data - adds any streamed output to StringBuilder, flags task completion
// when streams close (null data received):
var outputCompletionTask = new TaskCompletionSource <bool>();
process.OutputDataReceived += (sender, e) =>
{
if (e.Data == null)
{
outputCompletionTask.TrySetResult(true);
}
else
{
consoleOutput.AppendLine(e.Data);
}
};
// Set up event processing for process exit (return process return value via Task object):
process.EnableRaisingEvents = true;
var processCompletionTask = new TaskCompletionSource <int>();
process.Exited += (sender, e) =>
{
processCompletionTask.TrySetResult(process.ExitCode);
};
// Launch process, begin asynchronous reading of output:
process.Start();
process.BeginOutputReadLine();
// Wait for process to exit, then wait on output handles to close (to ensure all console output is read
// and streams are properly cleaned up):
int returnValue = await processCompletionTask.Task.ConfigureAwait(false);
await outputCompletionTask.Task.ConfigureAwait(false);
// If configuration does not specify a return value validation regex, assume success:
if (string.IsNullOrEmpty(returnValueRegex) ? true : Regex.IsMatch(returnValue.ToString(), returnValueRegex))
{
logger.LogInformation($"Process exited with code {returnValue}");
result.Success = true;
}
else
{
throw new Exception($"Invalid process exit code ({returnValue})");
}
}
}
catch (AggregateException ae) // Catches asynchronous exceptions only
{
throw TaskUtilities.General.SimplifyAggregateException(ae);
}
#endregion
}
catch (Exception ex)
{
logger.LogError(ex, "Command execution failed");
result.AddException(ex);
}
// If console output was captured, log now:
if (consoleOutput.Length > 0)
{
logger.LogDebug(consoleOutput.ToString());
}
return(result);
}
/// <summary>
/// Execute the specified SQL stored procedure (deriving parameters automatically, applying inputs from
/// parameters and adding output parameters to return values collection)
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var consoleOutput = new StringBuilder();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
// Retrieve and validate required parameters
string connectionString = config.GetConnectionString(parameters.GetString("ConnectionString"));
string storedProcedureName = parameters.GetString("StoredProcedureName");
if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(storedProcedureName))
{
throw new ArgumentException("Missing or invalid ConnectionString and/or StoredProcedureName");
}
// Retrieve optional parameters
string returnValueRegex = parameters.GetString("ReturnValueRegex");
bool defaultNulls = parameters.GetBool("DefaultNulls");
var dbTimeout = parameters.Get <int>("DBTimeout", int.TryParse);
#endregion
#region Open database connection and execute procedure
await using (var cnn = new SqlConnection(connectionString))
{
await cnn.OpenAsync();
// Capture console messages into StringBuilder:
cnn.InfoMessage += (object obj, SqlInfoMessageEventArgs e) => { consoleOutput.AppendLine(e.Message); };
await using (var cmd = new SqlCommand(storedProcedureName, cnn)
{
CommandType = CommandType.StoredProcedure
})
{
if (dbTimeout > 0)
{
cmd.CommandTimeout = (int)dbTimeout;
}
#region Derive and apply procedure parameters
SqlCommandBuilder.DeriveParameters(cmd); // Note: synchronous (no async version available)
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
if (sqlParameter.Direction.HasFlag(ParameterDirection.Input))
{
// This parameter requires input value - check for corresponding parameter:
if (parameters.ContainsKey(sqlParameter.ParameterName))
{
// Special case - strings are not automatically changed to Guid values, attempt explicit conversion:
if (sqlParameter.SqlDbType == SqlDbType.UniqueIdentifier)
{
sqlParameter.Value = (object)parameters.Get <Guid>(sqlParameter.ParameterName, Guid.TryParse) ?? DBNull.Value;
}
else
{
// Apply string value to parameter (replacing null with DBNull):
sqlParameter.Value = (object)parameters.GetString(sqlParameter.ParameterName, null, dateTimeNow) ?? DBNull.Value;
}
}
// If parameter value was not set above, and either we are set to supply default NULL
// values OR this is also an OUTPUT parameter, set to explicit NULL:
else if (defaultNulls || sqlParameter.Direction.HasFlag(ParameterDirection.Output))
{
sqlParameter.Value = DBNull.Value;
}
// (otherwise, value will be left unspecified; if stored procedure does not provide a default
// value, execution will fail and missing parameter will be indicated by exception string)
}
}
#endregion
await cmd.ExecuteNonQueryAsync();
#region Extract output parameters into return values collection, validate return value
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
if (sqlParameter.Direction.HasFlag(ParameterDirection.Output))
{
result.AddReturnValue(sqlParameter.ParameterName, sqlParameter.Value == DBNull.Value ? null : sqlParameter.Value?.ToString());
}
}
// If return value regex provided, check return value against it:
if (!string.IsNullOrEmpty(returnValueRegex))
{
string returnValue = result.ReturnValues.ContainsKey("@RETURN_VALUE") ? result.ReturnValues["@RETURN_VALUE"] : null;
if (string.IsNullOrEmpty(returnValue))
{
throw new Exception($"Stored procedure did not return a value (or no value retrieved)");
}
else if (!Regex.IsMatch(returnValue, returnValueRegex))
{
throw new Exception($"Invalid stored procedure return value ({returnValue})");
}
}
#endregion
// If this point is reached with no exception raised, operation was successful:
result.Success = true;
}
}
#endregion
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
logger.LogError(ex, "Procedure execution failed");
result.AddException(ex);
}
// If console output was captured, log now:
if (consoleOutput.Length > 0)
{
logger.LogDebug(consoleOutput.ToString());
}
return(result);
}
/// <summary>
/// Iterate through a specified folder (and optionally, subfolders), cleaning up files over a specified age
/// (optionally archiving them to zip file, first)
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
// Retrieve and validate required parameters first:
string sourceFolder = parameters.GetFolder("SourceFolder", dateTimeNow);
string filenameFilter = parameters.GetString("FilenameFilter", null, dateTimeNow);
var maxAge = parameters.Get <TimeSpan>("MaxAge", TimeSpan.TryParse);
if (string.IsNullOrEmpty(sourceFolder) || string.IsNullOrEmpty(filenameFilter) || maxAge == null)
{
throw new ArgumentException("Missing or invalid: one or more of SourceFolder/FilenameFilter/MaxAge");
}
// Ignore sign on TimeSpan value (allow time to be specified either way):
var minLastWriteTime = dateTimeNow.Add((TimeSpan)(maxAge?.TotalMilliseconds > 0 ? maxAge?.Negate() : maxAge));
// Retrieve optional parameters (general):
var filenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("FilenameRegex"), RegexOptions.IgnoreCase);
bool recurseFolders = parameters.GetBool("RecurseFolders");
// Retrieve optional archive parameters (indicating files should be zipped up prior to deletion); we will
// validate ArchiveFolder value here rather than as part of GetString, to ensure that if it is invalid the
// process is aborted rather than silently deleting files that should be archived:
string archiveFolder = parameters.GetString("ArchiveFolder");
if (!string.IsNullOrEmpty(archiveFolder))
{
if (!TaskUtilities.General.REGEX_FOLDERPATH.IsMatch(archiveFolder))
{
throw new ArgumentException("Invalid ArchiveFolder specified");
}
}
string archiveSubfolder = string.IsNullOrEmpty(archiveFolder) ? null : parameters.GetString("ArchiveSubfolder");
var archiveRenameRegex = string.IsNullOrEmpty(archiveFolder) ? null : TaskUtilities.General.RegexIfPresent(parameters.GetString("ArchiveRenameRegex"), RegexOptions.IgnoreCase);
string archiveRenameReplacement = archiveRenameRegex == null ? null : (parameters.GetString("ArchiveRenameReplacement") ?? string.Empty);
// Ensure sourceFolder ends with trailing slash, for proper relative paths in recursive folders:
if (!sourceFolder.EndsWith(@"\"))
{
sourceFolder += @"\";
}
// If custom regex not specified, create one from file filter (this check is performed to avoid false-positives on 8.3 version of filenames):
if (filenameRegex == null)
{
filenameRegex = TaskUtilities.General.RegexFromFileFilter(filenameFilter);
}
#endregion
// Build listing of all files in source folder whose last write time is older than minimum value:
var fileList = Directory.EnumerateFiles(sourceFolder, filenameFilter, recurseFolders ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
.Where(fileName => filenameRegex.IsMatch(Path.GetFileName(fileName)))
.Select(fileName => new { FileName = fileName, LastWriteTime = File.GetLastWriteTime(fileName) })
.Where(file => file.LastWriteTime < minLastWriteTime);
#region Handle all matching files
if (fileList.Any())
{
// Set up shared objects for use throughout remainder of process:
using var md5prov = IncrementalHash.CreateHash(HashAlgorithmName.MD5);
foreach (var file in fileList)
{
try
{
// Check for an ArchiveFolder value (if present, we need to zip up file prior to deleting):
if (!string.IsNullOrEmpty(archiveFolder))
{
#region Add file to zip archive
if (file.FileName.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
{
// Do not add zip files to zip archive (zip archives may be created within the folder
// structure we are currently cleaning, and we do not want to add zip to itself):
continue;
}
// Apply date/time values to archive folder name, ensure resulting folder exists:
string archiveFilePath = TaskUtilities.General.ApplyDateMacros(archiveFolder, file.LastWriteTime);
if (!Directory.Exists(archiveFilePath))
{
Directory.CreateDirectory(archiveFilePath);
}
// If regular expression/replacement not provided, append simple archive filename in date-based format:
if (archiveRenameRegex == null)
{
archiveFilePath = TaskUtilities.General.PathCombine(archiveFilePath, $"{file.LastWriteTime:yyyy-MM}.zip");
}
// Otherwise append archive filename constructed using regex/replacement:
else
{
archiveFilePath = TaskUtilities.General.PathCombine(archiveFilePath,
$"{archiveRenameRegex.Replace(Path.GetFileNameWithoutExtension(file.FileName), TaskUtilities.General.ApplyDateMacros(archiveRenameReplacement, file.LastWriteTime))}.zip");
}
// Open/create resulting zip archive:
using (var archive = ZipFile.Open(archiveFilePath, ZipArchiveMode.Update))
{
// Determine path within zip under which this file will be placed: start with path
// relative to our working folder (in case we are recursing subfolders), prefixed
// with explicit subfolder name, if configured:
string relativePath = Uri.UnescapeDataString(new Uri(sourceFolder).MakeRelativeUri(new Uri(file.FileName)).ToString());
if (!string.IsNullOrEmpty(archiveSubfolder))
{
relativePath = TaskUtilities.General.PathCombine(TaskUtilities.General.ApplyDateMacros(archiveSubfolder, file.LastWriteTime), relativePath);
}
// Check whether this path already exists within this zip archive:
var existingEntry = archive.GetEntry(relativePath);
if (existingEntry != null)
{
// Existing entry found - we only want to add this file again if the contents are not equal to the
// entry already in the archive, so calculate and compare file hashes:
byte[] hashInZip = null;
using (var zipStream = existingEntry.Open())
{
hashInZip = await TaskUtilities.Streams.GetStreamHash(zipStream, md5prov);
}
using (var fileStream = new FileStream(file.FileName, FileMode.Open, FileAccess.Read, FileShare.None))
{
// If zip hash sequence is NOT equal to the hash of the source file, clear existing entry
// to indicate that the file SHOULD be added (CreateEntryFromFile allows duplicates):
if (!hashInZip.SequenceEqual(await TaskUtilities.Streams.GetStreamHash(fileStream, md5prov)))
{
existingEntry = null;
}
}
}
// If there is no existing entry or entry was cleared above due to hash check, add to archive:
if (existingEntry == null)
{
archive.CreateEntryFromFile(file.FileName, relativePath);
logger.LogDebug($"File {file.FileName} added to archive {archiveFilePath}");
}
}
#endregion
}
// If this point is reached, file can be deleted:
File.Delete(file.FileName);
logger.LogInformation($"File {file.FileName} deleted");
}
catch (Exception ex)
{
// Exception cleaning individual file should not leak out of overall task; just log (simplify if necessary):
logger.LogWarning(ex is AggregateException ae ? TaskUtilities.General.SimplifyAggregateException(ae) : ex,
$"Error cleaning file {file.FileName}");
}
}
}
#endregion
// If this point is reached with no uncaught exceptions, return success
result.Success = true;
}
catch (Exception ex)
{
logger.LogError(ex, "Directory cleaning failed");
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Retrieve all files from the specified location (matching specified pattern), and import their contents
/// into the provided database using the stored procedure(s) specified
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
string connectionString = config.GetConnectionString(parameters.GetString("ConnectionString"));
importProcedureName = parameters.GetString("ImportProcedureName");
string importFolder = parameters.GetFolder("ImportFolder", dateTimeNow);
string filenameFilter = parameters.GetString("FilenameFilter", null, dateTimeNow);
if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(importProcedureName) || string.IsNullOrEmpty(importFolder) || string.IsNullOrEmpty(filenameFilter))
{
throw new ArgumentException("Missing or invalid: one or more of ConnectionString/ImportProcedureName/ImportFolder/FilenameFilter");
}
// Retrieve import mode (default is RAW), validate if present:
importMode = parameters.GetString("ImportMode")?.ToUpperInvariant();
if (!string.IsNullOrEmpty(importMode))
{
if (!ImportModeValidationRegex.IsMatch(importMode))
{
throw new ArgumentException("Invalid ImportMode");
}
}
// Retrieve data parameter name, validate if present:
importDataParameterName = parameters.GetString("ImportDataParameterName");
if (!string.IsNullOrEmpty(importDataParameterName))
{
if (!importDataParameterName.StartsWith("@"))
{
throw new ArgumentException("Invalid ImportDataParameterName");
}
}
// Retrieve imported file archival settings (either folder or rename regex required):
string archiveFolder = parameters.GetFolder("ArchiveFolder", dateTimeNow);
var archiveRenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("ArchiveRenameRegex"), RegexOptions.IgnoreCase);
string archiveRenameReplacement = archiveRenameRegex == null ? null : (parameters.GetString("ArchiveRenameReplacement", null, dateTimeNow) ?? string.Empty);
if (string.IsNullOrEmpty(archiveFolder))
{
if (archiveRenameRegex == null)
{
throw new ArgumentException("Either ArchiveFolder or ArchiveRenameRegex settings required");
}
}
// Create archival folder, if it doesn't already exist:
else if (!Directory.Exists(archiveFolder))
{
Directory.CreateDirectory(archiveFolder);
}
// Retrieve optional parameters:
importProcedureTimeout = parameters.Get <int>("ImportProcedureTimeout", int.TryParse);
importPreProcessorName = parameters.GetString("ImportPreProcessorName");
importPreProcessorTimeout = parameters.Get <int>("ImportPreProcessorTimeout", int.TryParse);
importPostProcessorName = parameters.GetString("ImportPostProcessorName");
importPostProcessorTimeout = parameters.Get <int>("ImportPostProcessorTimeout", int.TryParse);
importPGPPrivateKeyRing = parameters.GetString("ImportPGPPrivateKeyRing");
importPGPPassphrase = parameters.GetString("ImportPGPPassphrase");
defaultNulls = parameters.GetBool("DefaultNulls");
var filenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("FilenameRegex"), RegexOptions.IgnoreCase);
delimiter = parameters.GetString("Delimiter");
if (string.IsNullOrEmpty(delimiter))
{
delimiter = ",";
}
importLineFilterRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("ImportLineFilterRegex"));
bool haltOnImportError = parameters.GetBool("HaltOnImportError");
// Add any parameters to be applied to SQL statements to dictionary:
var atparms = parameters.GetKeys().Where(parmname => parmname.StartsWith("@"));
foreach (var atparm in atparms)
{
sqlParameters[atparm] = parameters.GetString(atparm, null, dateTimeNow);
}
// If custom regex not specified, create one from file filter (this check is performed to avoid false-positives on 8.3 version of filenames):
if (filenameRegex == null)
{
filenameRegex = TaskUtilities.General.RegexFromFileFilter(filenameFilter);
}
#endregion
// Build listing of all matching files in import folder:
var fileInfoList = Directory.EnumerateFiles(importFolder, filenameFilter)
.Where(fileName => filenameRegex.IsMatch(Path.GetFileName(fileName)))
.Select(fileName => new FileInfo(fileName));
if (fileInfoList.Any())
{
#region Connect to database and process all files in list
await using (var cnn = new SqlConnection(connectionString))
{
await cnn.OpenAsync();
// Capture console messages into StringBuilder:
var consoleOutput = new StringBuilder();
cnn.InfoMessage += (object obj, SqlInfoMessageEventArgs e) => { consoleOutput.AppendLine(e.Message); };
foreach (var fileInfo in fileInfoList)
{
using var importFileScope = logger.BeginScope(new Dictionary <string, object>()
{
["FileName"] = fileInfo.FullName
});
// Determine path to archive file to after completion of import process:
var archiveFilePath = TaskUtilities.General.PathCombine(string.IsNullOrEmpty(archiveFolder) ? importFolder : archiveFolder,
archiveRenameRegex == null ? fileInfo.Name : archiveRenameRegex.Replace(fileInfo.Name, archiveRenameReplacement));
if (archiveFilePath.Equals(fileInfo.FullName, StringComparison.OrdinalIgnoreCase))
{
logger.LogWarning($"Import and archive folders are the same, file will not be archived");
archiveFilePath = null; // Ensure file move is skipped over below
}
try
{
// Create fileControlBlock to hold file information and data streams, then pass processing off to
// utility function, receiving any output parameters from import procedure and merging into overall
// task return value set:
using (var fileControlBlock = new FileControlBlock(fileInfo))
{
result.MergeReturnValues(await ImportFile(cnn, consoleOutput, fileControlBlock));
}
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
// Log error here (to take advantage of logger scope context) and add exception to result collection:
logger.LogError(ex, "Error importing file");
result.AddException(new Exception($"Error importing file {fileInfo.FullName}", ex));
// If we are halting process for individual import errors, throw custom exception to indicate
// to outer try block that it can just exit without re-logging:
if (haltOnImportError)
{
archiveFilePath = null; // Prevent finally block from archiving this file
throw new TaskExitException();
}
}
finally
{
// Unless file archival has been explicitly cancelled, move file to archive (regardless of result):
if (archiveFilePath != null)
{
try
{
File.Move(fileInfo.FullName, archiveFilePath);
logger.LogDebug($"File archived to {archiveFilePath}");
}
catch (Exception ex)
{
// Add exception to response collection, do not re-throw (to avoid losing actual valuable
// exception that may currently be throwing to outer block)
result.AddException(new Exception($"Error archiving file {fileInfo.FullName} to {archiveFilePath}", ex));
}
}
// Import procedure should have consumed any console output already; ensure it is cleared for next run:
if (consoleOutput.Length > 0)
{
logger.LogDebug($"Unhandled console output follows:\n{consoleOutput}");
consoleOutput.Clear();
}
}
}
}
#endregion
}
// If this point is reached, consider overall operation successful
result.Success = true;
}
catch (TaskExitException)
{
// Exception was handled above - just proceed with return below
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
logger.LogError(ex, "Import process failed");
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Move or copy all files matching a pattern from a source location to a destination location, optionally
/// encrypting/decrypting/renaming in the process
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
bool suppressErrors = false; // If true, errors will not be factored into success/failure of this task
Stream downloadListFile = null; // If used, download listing file will be held open until finally block
try
{
#region Retrieve task parameters
// Load source connection configuration and validate:
var sourceConnectionConfig = new ConnectionConfig(parameters, true);
if (sourceConnectionConfig.Invalid)
{
throw new ArgumentException("Source configuration invalid");
}
// Load destination connection configuration and validate:
var destConnectionConfig = new ConnectionConfig(parameters, false);
if (destConnectionConfig.Invalid)
{
throw new ArgumentException("Destination configuration invalid");
}
// Build collection of source file path/filter combinations starting with standard single values in parameter collection:
var sourceFilePaths = new List <SourceFilePath>
{
new SourceFilePath
{
FolderPath = parameters.GetString("SourceFolderPath", null, dateTimeNow),
FilenameFilter = parameters.GetString("SourceFilenameFilter", null, dateTimeNow),
FilenameRegex = parameters.GetString("SourceFilenameRegex")
}
};
// Add path collection from separate configuration section, if present:
var sourcePathsConfig = parameters.Configuration.GetSection("SourceFilePaths");
if (sourcePathsConfig.Exists())
{
sourceFilePaths.AddRange(sourcePathsConfig.Get <IEnumerable <SourceFilePath> >());
}
// Remove invalid entries and ensure there are source paths present:
sourceFilePaths.RemoveAll(path => path.Invalid());
if (sourceFilePaths.Count == 0)
{
throw new ArgumentException("No valid source file paths found");
}
// Read optional parameters - behavior:
suppressErrors = parameters.GetBool("SuppressErrors");
var maxAge = parameters.Get <TimeSpan>("MaxAge", TimeSpan.TryParse);
var preventOverwrite = parameters.GetBool("PreventOverwrite");
var copyFileOnly = parameters.GetBool("CopyFileOnly");
// Read optional parameters - rename file at destination:
var fileRenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("FileRenameRegex"), RegexOptions.IgnoreCase);
string fileRenameReplacement = null;
bool fileRenameDefer = false; // If true, file will be renamed after transfer complete
if (fileRenameRegex != null)
{
fileRenameDefer = parameters.GetBool("FileRenameDefer");
if (fileRenameDefer && preventOverwrite)
{
// Deferred rename occurs at remote site; with preventOverwrite on, original file may be uploaded under another name
// by destination object (meaning rename step would fail), and final filename will not be checked in advance:
throw new ArgumentException("PreventOverwrite and FileRenameDefer options cannot be used together");
}
fileRenameReplacement = parameters.GetString("FileRenameReplacement", null, dateTimeNow) ?? string.Empty;
}
// Read optional parameters - rename source file after copy:
var sourceFileRenameRegex = TaskUtilities.General.RegexIfPresent(parameters.GetString("SourceFileRenameRegex"), RegexOptions.IgnoreCase);
string sourceFileRenameReplacement = null;
if (sourceFileRenameRegex != null)
{
if (!copyFileOnly)
{
// If CopyFileOnly is not set, source file will be deleted (and thus cannot be renamed); raise error to ensure that
// unintended behavior does not occur (allow opportunity to correct configuration):
throw new ArgumentException("SourceFileRenameRegex cannot be used unless CopyFileOnly is set");
}
sourceFileRenameReplacement = parameters.GetString("SourceFileRenameReplacement", null, dateTimeNow) ?? string.Empty;
}
#endregion
#region If file download history listing is configured, open file and read
DownloadFileList downloadFileList = null;
string downloadListFilename = parameters.GetString("DownloadListFilename");
if (!string.IsNullOrEmpty(downloadListFilename))
{
if (File.Exists(downloadListFilename))
{
// Open file (note this FileStream will be kept open until finally block below) and attempt to
// deserialize file listing from its contents (if any):
downloadListFile = new FileStream(downloadListFilename, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
downloadFileList = downloadListFile.Length > 0 ? await JsonSerializer.DeserializeAsync <DownloadFileList>(downloadListFile) : new DownloadFileList();
// If there are any files in listing, prune list by download age:
if (downloadFileList?.downloadFiles?.Count > 0)
{
if (downloadFileList.PruneList(parameters.Get <TimeSpan>("DownloadListMaxAge", TimeSpan.TryParse)))
{
// File listing was modified - truncate file and re-write contents:
downloadListFile.SetLength(0);
await JsonSerializer.SerializeAsync(downloadListFile, downloadFileList, downloadListFileFormat);
}
}
}
else
{
// Create new empty file (leave stream open for use below) and new empty listing:
downloadListFile = new FileStream(downloadListFilename, FileMode.Create, FileAccess.Write, FileShare.None);
downloadFileList = new DownloadFileList();
}
}
#endregion
// Create source connection, connect and retrieve file listing
using (var sourceConnection = Connection.CreateInstance(isp, sourceConnectionConfig))
{
sourceConnection.Connect();
var fileList = sourceConnection.GetFileList(sourceFilePaths);
if (fileList.Any() && downloadFileList?.downloadFiles?.Count > 0)
{
// Remove any files already present in downloaded list from current set:
fileList.ExceptWith(downloadFileList.downloadFiles);
}
if (fileList.Any() && maxAge != null)
{
// Remove any files older than maxAge (ignoring sign - allow age to be specified either way):
var minLastWriteTime = dateTimeNow.Add((TimeSpan)(maxAge?.TotalMilliseconds > 0 ? maxAge?.Negate() : maxAge));
fileList.RemoveWhere(file => file.lastWriteTime < minLastWriteTime);
}
if (fileList.Any())
{
#region Open destination connection and transfer files
using var destConnection = Connection.CreateInstance(isp, destConnectionConfig);
destConnection.Connect();
foreach (var file in fileList)
{
using var logscope = new TaskUtilities.LogScopeHelper(logger, new Dictionary <string, object>()
{
["FileFolder"] = file.fileFolder,
["FileName"] = file.fileName,
["SourcePGP"] = sourceConnectionConfig.PGP,
["DestPGP"] = destConnectionConfig.PGP
});
try
{
#region Perform file transfer
// Apply file rename, if appropriate:
string destFileName = (fileRenameRegex == null || fileRenameDefer) ? file.fileName : fileRenameRegex.Replace(file.fileName, fileRenameReplacement);
logger.LogDebug($"File will be transferred to {(string.IsNullOrEmpty(file.DestinationSubfolder) ? destFileName : TaskUtilities.General.PathCombine(file.DestinationSubfolder, destFileName))}");
// If simple copy method supported by this source/dest combination, perform now:
if (destConnection.SupportsSimpleCopy(sourceConnection))
{
string destPath = destConnection.DoSimpleCopy(sourceConnection, file.fileFolder, file.fileName, file.DestinationSubfolder, destFileName, preventOverwrite);
logscope.AddToState("DestFilePath", destPath);
logger.LogInformation("File transferred (using simple copy)");
}
else // Non-folder source/destination or PGP encryption/decryption required
{
// Request write stream from destination object:
using var deststream = destConnection.GetWriteStream(file.DestinationSubfolder, destFileName, preventOverwrite);
logscope.AddToState("DestFilePath", deststream.path);
if (destConnectionConfig.PGP)
{
// Destination requires PGP encryption - create encrypting stream around destination stream:
using var encstream = destConnectionConfig.pgpRawFormat ?
await TaskUtilities.Pgp.GetEncryptionStreamRaw(destConnection.PGPKeyStream, destConnectionConfig.pgpUserID, deststream.stream)
: await TaskUtilities.Pgp.GetEncryptionStream(destConnection.PGPKeyStream, destConnectionConfig.pgpUserID, deststream.stream);
// Order source connection to write data from specified file into encrypting stream:
await sourceConnection.DoTransfer(file.fileFolder, file.fileName, encstream.GetStream());
}
else
{
// No encryption required - order source connection to write data from specified file into destination stream:
await sourceConnection.DoTransfer(file.fileFolder, file.fileName, deststream.stream);
}
await destConnection.FinalizeWrite(deststream);
logger.LogInformation("File transferred");
}
// If file rename deferred, perform now:
if (fileRenameDefer)
{
var renameFile = fileRenameRegex.Replace(destFileName, fileRenameReplacement);
logscope.AddToState("DestFileRenamePath", renameFile);
destConnection.DestRenameFile(file.DestinationSubfolder, destFileName, renameFile, preventOverwrite);
logger.LogInformation("File renamed at destination", renameFile);
}
#endregion
#region Delete or rename source file
if (copyFileOnly)
{
if (sourceFileRenameRegex != null)
{
var renameFile = sourceFileRenameRegex.Replace(file.fileName, sourceFileRenameReplacement);
sourceConnection.SourceRenameFile(file.fileFolder, file.fileName, renameFile, preventOverwrite);
logger.LogDebug($"Source file renamed to {renameFile}");
}
}
else
{
sourceConnection.DeleteFile(file.fileFolder, file.fileName);
logger.LogDebug("Source file deleted");
}
#endregion
}
catch (Exception ex)
{
logger.LogError(ex, "Error downloading file");
if (!suppressErrors)
{
result.AddException(new Exception($"Error downloading file {TaskUtilities.General.PathCombine(file.fileFolder, file.fileName)}", ex));
}
continue;
}
#region Update file download list, if required
if (downloadFileList != null)
{
try
{
// Clear file contents and re-serialize updated file listing (performed after each file transfer rather than
// at end of job to ensure that a scenario where some files are downloaded and then the job/task is halted
// or killed does not result in duplicate files being downloaded next run)
downloadFileList.downloadFiles.Add(file);
downloadListFile.SetLength(0);
await JsonSerializer.SerializeAsync(downloadListFile, downloadFileList, downloadListFileFormat);
}
catch (Exception ex)
{
logger.LogError(ex, "Error updating file download listing");
result.AddException(new Exception("Error updating download listing", ex));
}
}
#endregion
}
destConnection.Disconnect();
#endregion
}
sourceConnection.Disconnect();
}
// If this point is reached with no exceptions thrown, set success:
result.Success = true;
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
logger.LogError(ex, "Move process failed");
// Error has been logged; if we are suppressing errors consider this successful, otherwise add
// exception to return collection for caller to handle:
if (suppressErrors)
{
result.Success = true;
}
else
{
result.AddException(ex);
}
}
finally
{
if (downloadListFile != null)
{
await downloadListFile.DisposeAsync();
downloadListFile = null;
}
}
return(result);
}
