public override ExitCodeType Attach(IDataLoadJob job, GracefulCancellationToken cancellationToken)
{
if (job == null)
throw new Exception("Job is Null, we require to know the job to build a DataFlowPipeline");
ThrowIfInvalidRemoteTableName();
var syntax = _remoteDatabase.Server.GetQuerySyntaxHelper();
string sql;
if (!string.IsNullOrWhiteSpace(RemoteSelectSQL))
sql = RemoteSelectSQL;
else
sql = "Select * from " + syntax.EnsureWrapped(RemoteTableName);
bool scheduleMismatch = false;
//if there is a load progress
if (Progress != null)
try
{
//get appropriate date declaration SQL if any
sql = GetScheduleParameterDeclarations(job, out scheduleMismatch) + sql;
}
catch (Exception e)
{
//if the date range is in the future then GetScheduleParameterDeclarations will throw Exception about future dates
if(e.Message.StartsWith(FutureLoadMessage))
return ExitCodeType.OperationNotRequired;//if this is the case then don't bother with the data load
throw;
}
if (scheduleMismatch)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping LoadProgress '" + Progress + "' because it is not the correct Schedule for this component"));
return ExitCodeType.Success;
}
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to execute SQL:" + Environment.NewLine + sql));
var source = new DbDataCommandDataFlowSource(sql, "Fetch data from " + _remoteDatabase.Server + " to populate RAW table " + RemoteTableName, _remoteDatabase.Server.Builder, Timeout == 0 ? 50000 : Timeout);
//For Oracle / Postgres we have to add the parameters to the DbCommand directly
if (_minDateParam.HasValue && _maxDateParam.HasValue && !syntax.SupportsEmbeddedParameters())
{
source.CommandAdjuster = (cmd) =>
{
var pmin = cmd.CreateParameter();
pmin.Value = _minDateParam.Value;
pmin.ParameterName = StartDateParameter;
cmd.Parameters.Add(pmin);
var pmax = cmd.CreateParameter();
pmax.Value = _maxDateParam.Value;
pmax.ParameterName = EndDateParameter;
cmd.Parameters.Add(pmax);
};
}
var destination = new SqlBulkInsertDestination(_dbInfo, RAWTableName, Enumerable.Empty<string>());
var contextFactory = new DataFlowPipelineContextFactory<DataTable>();
var context = contextFactory.Create(PipelineUsage.LogsToTableLoadInfo | PipelineUsage.FixedDestination);
var engine = new DataFlowPipelineEngine<DataTable>(context, source, destination, job);
ITableLoadInfo loadInfo = job.DataLoadInfo.CreateTableLoadInfo("Truncate RAW table " + RAWTableName,
_dbInfo.Server.Name + "." + _dbInfo.GetRuntimeName(),
new []
{
new DataSource(
"Remote SqlServer Servername=" + _remoteDatabase.Server + "Database=" + _dbInfo.GetRuntimeName() +
//Either list the table or the query depending on what is populated
(RemoteTableName != null?" Table=" + RemoteTableName
:" Query = " + sql), DateTime.Now)
}, -1);
engine.Initialize(loadInfo);
engine.ExecutePipeline(new GracefulCancellationToken());
if (source.TotalRowsRead == 0 && LoadNotRequiredIfNoRowsRead)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "No rows were read from the remote table and LoadNotRequiredIfNoRowsRead is true so returning ExitCodeType.LoadNotRequired"));
return ExitCodeType.OperationNotRequired;
}
job.OnNotify(this, new NotifyEventArgs(source.TotalRowsRead > 0 ? ProgressEventType.Information:ProgressEventType.Warning, "Finished after reading " + source.TotalRowsRead + " rows"));
if (Progress != null)
{
if(ProgressUpdateStrategy == null)
throw new Exception("ProgressUpdateStrategy is null but there is a Progress");
ProgressUpdateStrategy.AddAppropriateDisposeStep((ScheduledDataLoadJob) job,_dbInfo);
}
return ExitCodeType.Success;
}
public override void Check(ICheckNotifier notifier)
{
if (RemoteServerReference != null)
{
if (!string.IsNullOrWhiteSpace(RemoteServer))
notifier.OnCheckPerformed(new CheckEventArgs("RemoteServer must be blank when you have specified a RemoteServerReference", CheckResult.Fail));
if (!string.IsNullOrWhiteSpace(RemoteDatabaseName))
notifier.OnCheckPerformed(new CheckEventArgs("RemoteDatabaseName must be blank when you have specified a RemoteServerReference", CheckResult.Fail));
if(RemoteTableAccessCredentials != null)
notifier.OnCheckPerformed(new CheckEventArgs("RemoteTableAccessCredentials must be blank when you have specified a RemoteServerReference", CheckResult.Fail));
if (string.IsNullOrWhiteSpace(RemoteServerReference.Database))
notifier.OnCheckPerformed(new CheckEventArgs($"RemoteServerReference {RemoteServerReference} had no listed database to connect to", CheckResult.Fail));
}
else
{
if (string.IsNullOrWhiteSpace(RemoteServer))
notifier.OnCheckPerformed(new CheckEventArgs("RemoteServer is a Required field (unless you specify a RemoteServerReference)", CheckResult.Fail));
if (string.IsNullOrWhiteSpace(RemoteDatabaseName))
notifier.OnCheckPerformed(new CheckEventArgs("RemoteDatabaseName is a Required field (unless you specify a RemoteServerReference)", CheckResult.Fail));
}
//if we have been initialized
if (LoadDirectory != null)
{
try
{
ThrowIfInvalidRemoteTableName();
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Failed to find username and password for RemoteTableAttacher",
CheckResult.Fail, e));
}
try
{
try
{
Setup();
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Failed to setup username/password - proceeding with Integrated Security", CheckResult.Warning, e));
}
CheckTablesExist(notifier);
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Program crashed while trying to inspect remote server " + (RemoteServerReference ?? (object)RemoteServer ) + " for presence of tables/databases specified in the load configuration.",
CheckResult.Fail, e));
}
}
else
notifier.OnCheckPerformed(new CheckEventArgs(
"LoadDirectory was null in Check() for class RemoteTableAttacher",
CheckResult.Warning, null));
if (Progress != null)
{
if (!Progress.DataLoadProgress.HasValue)
{
if (Progress.OriginDate.HasValue)
{
var fixDate = Progress.OriginDate.Value.AddDays(-1);
bool setDataLoadProgressDateToOriginDate = notifier.OnCheckPerformed(new CheckEventArgs("LoadProgress '" + Progress + "' does not have a DataLoadProgress value, you must set this to something to start loading data from that date", CheckResult.Fail, null, "Set the data load progress date to the OriginDate minus one Day? " + Environment.NewLine + "Set DataLoadProgress = " + Progress.OriginDate + " -1 day = " + fixDate));
if(setDataLoadProgressDateToOriginDate)
{
Progress.DataLoadProgress = fixDate;
Progress.SaveToDatabase();
}
else
notifier.OnCheckPerformed(new CheckEventArgs("User decided not to apply suggested fix so stopping checking",CheckResult.Fail, null));
}
else
notifier.OnCheckPerformed(new CheckEventArgs("LoadProgress '" + Progress + "' does not have a DataLoadProgress value, you must set this to something to start loading data from that date",CheckResult.Fail, null));
}
if (ProgressUpdateStrategy == null)
notifier.OnCheckPerformed(
new CheckEventArgs(
"Progress is specified '" + Progress +
"' but there is no ProgressUpdateStrategy specified (if you have one you must have both)",
CheckResult.Fail));
else
ProgressUpdateStrategy.Check(notifier);
if (!LoadNotRequiredIfNoRowsRead)
notifier.OnCheckPerformed(new CheckEventArgs("LoadNotRequiredIfNoRowsRead is false but you have a Progress '" + Progress +"', this means that when the data being loaded is fully exhausted for a given range of days you will probably get an error instead of a clean shutdown",CheckResult.Warning));
if(string.IsNullOrWhiteSpace(RemoteSelectSQL))
notifier.OnCheckPerformed(new CheckEventArgs("A LoadProgress has been configured but the RemoteSelectSQL is empty, how are you respecting the schedule without tailoring your query?", CheckResult.Fail, null));
else
{
foreach (string expectedParameter in new[] {StartDateParameter, EndDateParameter})
if (RemoteSelectSQL.Contains(expectedParameter))
notifier.OnCheckPerformed(new CheckEventArgs("Found " + expectedParameter + " in the RemoteSelectSQL",
CheckResult.Success, null));
else
notifier.OnCheckPerformed(new CheckEventArgs(
"Could not find any reference to parameter " + expectedParameter +
" in the RemoteSelectSQL, how do you expect to respect the LoadProgress you have configured without a reference to this date?", CheckResult.Fail, null));
}
}
if (string.IsNullOrWhiteSpace(RAWTableName))
notifier.OnCheckPerformed(new CheckEventArgs("RAWTableName has not been set for " + GetType().Name, CheckResult.Fail));
}
