/// <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>
/// 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>
/// 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);
}
