public static void CreateTaskInstance(Logging logging)
{
logging.LogInformation("Create ScheduleInstance called.");
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
DateTimeOffset _date = DateTimeOffset.Now;
DataTable dtScheduleInstance = new DataTable();
dtScheduleInstance.Columns.Add(new DataColumn("ScheduleMasterId", typeof(long)));
dtScheduleInstance.Columns.Add(new DataColumn("ScheduledDateUtc", typeof(DateTime)));
dtScheduleInstance.Columns.Add(new DataColumn("ScheduledDateTimeOffset", typeof(DateTimeOffset)));
dtScheduleInstance.Columns.Add(new DataColumn("ActiveYN", typeof(bool)));
dynamic resScheduleInstance = TMD.GetSqlConnection().QueryWithRetry(@"
Select
SM.ScheduleMasterId,
SM.ScheduleCronExpression,
Coalesce(SI.MaxScheduledDateTimeOffset,cast('1900-01-01' as datetimeoffset)) as MaxScheduledDateTimeOffset
from
ScheduleMaster SM
join (
Select distinct ScheduleMasterId from TaskMaster TM where TM.ActiveYN = 1) TM on TM.ScheduleMasterId = SM.ScheduleMasterId
left outer join
(
Select ScheduleMasterId, Max(ScheduledDateTimeOffset) MaxScheduledDateTimeOffset
From ScheduleInstance
Where ActiveYN = 1
Group By ScheduleMasterId
) SI on SM.ScheduleMasterId = SI.ScheduleMasterId
Where SM.ActiveYN = 1");
foreach (dynamic _row in resScheduleInstance)
{
DateTimeOffset?nextUtc;
if (_row.ScheduleCronExpression.ToString() == "N/A")
{
nextUtc = DateTime.UtcNow.AddMinutes(-1);
}
else
{
CronExpression _cronExpression = CronExpression.Parse(_row.ScheduleCronExpression.ToString(), CronFormat.IncludeSeconds);
nextUtc = _cronExpression.GetNextOccurrence(_row.MaxScheduledDateTimeOffset, TimeZoneInfo.Utc);
}
if (nextUtc?.DateTime <= DateTime.UtcNow)
{
DataRow dr = dtScheduleInstance.NewRow();
dr["ScheduleMasterId"] = _row.ScheduleMasterId;
dr["ScheduledDateUtc"] = _date.Date;
dr["ScheduledDateTimeOffset"] = _date;
dr["ActiveYN"] = true;
dtScheduleInstance.Rows.Add(dr);
}
}
//Persist TEMP ScheduleInstance
SqlConnection _con = TMD.GetSqlConnection();
Table tmpScheduleInstanceTargetTable = new Table
{
Name = "#Temp" + Guid.NewGuid().ToString()
};
TMD.BulkInsert(dtScheduleInstance, tmpScheduleInstanceTargetTable, true, _con);
//Create TaskInstance
logging.LogInformation("Create TaskInstance called.");
DataTable dtTaskInstance = new DataTable();
dtTaskInstance.Columns.Add(new DataColumn("ExecutionUid", typeof(Guid)));
dtTaskInstance.Columns.Add(new DataColumn("TaskMasterId", typeof(long)));
dtTaskInstance.Columns.Add(new DataColumn("ScheduleInstanceId", typeof(long)));
dtTaskInstance.Columns.Add(new DataColumn("ADFPipeline", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("TaskInstanceJson", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("LastExecutionStatus", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("ActiveYN", typeof(bool)));
dynamic resTaskInstance = TMD.GetSqlConnection().QueryWithRetry(@"Exec dbo.GetTaskMaster");
DataTable dtTaskTypeMapping = GetTaskTypeMapping(logging);
foreach (dynamic _row in resTaskInstance)
{
DataRow drTaskInstance = dtTaskInstance.NewRow();
logging.DefaultActivityLogItem.TaskInstanceId = _row.TaskInstanceId;
logging.DefaultActivityLogItem.TaskMasterId = _row.TaskMasterId;
try
{
dynamic sourceSystemJson = JsonConvert.DeserializeObject(_row.SourceSystemJSON);
dynamic taskMasterJson = JsonConvert.DeserializeObject(_row.TaskMasterJSON);
dynamic targetSystemJson = JsonConvert.DeserializeObject(_row.TargetSystemJSON);
string _ADFPipeline = GetTaskTypeMappingName(logging, _row.TaskExecutionType.ToString(), dtTaskTypeMapping, _row.TaskTypeId, _row.SourceSystemType.ToString(), taskMasterJson?.Source.Type.ToString(), _row.TargetSystemType.ToString(), taskMasterJson?.Target.Type.ToString(), _row.TaskDatafactoryIR);
drTaskInstance["TaskMasterId"] = _row.TaskMasterId ?? DBNull.Value;
drTaskInstance["ScheduleInstanceId"] = 0;//_row.ScheduleInstanceId == null ? DBNull.Value : _row.ScheduleInstanceId;
drTaskInstance["ExecutionUid"] = logging.DefaultActivityLogItem.ExecutionUid;
drTaskInstance["ADFPipeline"] = _ADFPipeline;
drTaskInstance["LastExecutionStatus"] = "Untried";
drTaskInstance["ActiveYN"] = true;
JObject Root = new JObject();
if (_row.SourceSystemType == "ADLS" || _row.SourceSystemType == "Azure Blob")
{
if (taskMasterJson?.Source.Type.ToString() != "Filelist")
{
Root["SourceRelativePath"] = TaskInstancesStatic.TransformRelativePath(JObject.Parse(_row.TaskMasterJSON)["Source"]["RelativePath"].ToString(), _date.DateTime);
}
}
if (_row.TargetSystemType == "ADLS" || _row.TargetSystemType == "Azure Blob")
{
if (JObject.Parse(_row.TaskMasterJSON)["Target"]["RelativePath"] != null)
{
Root["TargetRelativePath"] = TaskInstancesStatic.TransformRelativePath(JObject.Parse(_row.TaskMasterJSON)["Target"]["RelativePath"].ToString(), _date.DateTime);
}
}
if (JObject.Parse(_row.TaskMasterJSON)["Source"]["IncrementalType"] == "Watermark")
{
Root["IncrementalField"] = _row.TaskMasterWaterMarkColumn;
Root["IncrementalColumnType"] = _row.TaskMasterWaterMarkColumnType;
if (_row.TaskMasterWaterMarkColumnType == "DateTime")
{
Root["IncrementalValue"] = _row.TaskMasterWaterMark_DateTime ?? "1900-01-01";
}
else if (_row.TaskMasterWaterMarkColumnType == "BigInt")
{
Root["IncrementalValue"] = _row.TaskMasterWaterMark_BigInt ?? -1;
}
}
if (Root == null)
{
drTaskInstance["TaskInstanceJson"] = DBNull.Value;
}
else
{
drTaskInstance["TaskInstanceJson"] = Root;
}
dtTaskInstance.Rows.Add(drTaskInstance);
}
catch (Exception e)
{
logging.LogErrors(new Exception(string.Format("Failed to create new task instances for TaskMasterId '{0}'.", logging.DefaultActivityLogItem.TaskInstanceId)));
logging.LogErrors(e);
}
}
//Persist TMP TaskInstance
Table tmpTaskInstanceTargetTable = new Table
{
Name = "#Temp" + Guid.NewGuid().ToString()
};
TMD.BulkInsert(dtTaskInstance, tmpTaskInstanceTargetTable, true, _con);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "tmpScheduleInstance", tmpScheduleInstanceTargetTable.QuotedSchemaAndName() },
{ "tmpTaskInstance", tmpTaskInstanceTargetTable.QuotedSchemaAndName() }
};
string InsertSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "InsertScheduleInstance_TaskInstance", SqlParams);
_con.ExecuteWithRetry(InsertSQL);
_con.Close();
}
