public static JObject Mapping(JArray arr, string sourceType, string targetType, string metadataType)
{
JObject Header = new JObject
{
["type"] = "TabularTranslator",
["typeConversion"] = true
};
JObject typeConversionSettings = new JObject
{
["allowDataTruncation"] = true,
["treatBooleanAsNumber"] = false
};
Header["typeConversionSettings"] = typeConversionSettings;
List <JObject> obj = new List <JObject>();
foreach (JObject r in arr)
{
JObject mappings = new JObject();
JObject source = new JObject();
JObject sink = new JObject();
if (metadataType == "SQL" && (sourceType == "Azure SQL" || sourceType == "SQL Server") && (targetType == "Azure Blob" || targetType == "ADLS"))
{
source["name"] = r["COLUMN_NAME"].ToString();
source["type"] = TaskInstancesStatic.TransformSQLTypesToDotNetFramework(r["DATA_TYPE"].ToString());
source["physicalType"] = r["DATA_TYPE"].ToString();
sink["name"] = TaskInstancesStatic.TransformParquetFileColName(r["COLUMN_NAME"].ToString());
sink["type"] = TaskInstancesStatic.TransformSQLTypesToParquet(r["DATA_TYPE"].ToString());
sink["physicalType"] = r["DATA_TYPE"].ToString();
}
else if (metadataType == "Parquet" && (sourceType == "Azure Blob" || sourceType == "ADLS") && (targetType == "SQL Server" || targetType == "Azure SQL" || targetType == "Table"))
{
source["name"] = TaskInstancesStatic.TransformParquetFileColName(r["COLUMN_NAME"].ToString());
source["type"] = TaskInstancesStatic.TransformSQLTypesToParquet(r["DATA_TYPE"].ToString());
source["physicalType"] = r["DATA_TYPE"].ToString();
sink["name"] = r["COLUMN_NAME"].ToString();
sink["type"] = TaskInstancesStatic.TransformSQLTypesToDotNetFramework(r["DATA_TYPE"].ToString());
sink["physicalType"] = r["DATA_TYPE"].ToString();
}
mappings["source"] = source;
mappings["sink"] = sink;
obj.Add(mappings);
}
Header["mappings"] = JToken.FromObject(obj);
JObject Root = new JObject
{
["value"] = Header
};
return(Root);
}
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();
}
