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