public static dynamic PrepareFrameworkTasksCore(Logging logging)
{
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
TMD.ExecuteSql(string.Format("Insert into Execution values ('{0}', '{1}', '{2}')", logging.DefaultActivityLogItem.ExecutionUid, DateTimeOffset.Now.ToString("u"), DateTimeOffset.Now.AddYears(999).ToString("u")));
//Check status of running pipelines and calculate available "slots" based on max concurrency settings
short _FrameworkWideMaxConcurrency = Shared.GlobalConfigs.GetInt16Config("FrameworkWideMaxConcurrency");
//ToDo: Write Pipelines that need to be checked to Queue for now I have just reduced to only those tasks that have been running for longer than x minutes.
//CheckLongRunningPipelines(logging);
//Get Count of All runnning pipelines directly from the database
short _RunnningPipelines = CountRunnningPipelines(logging);
short _AvailableConcurrencySlots = (short)(_FrameworkWideMaxConcurrency - _RunnningPipelines);
//Generate new task instances based on task master and schedules
CreateTaskInstance(logging);
//Is there is Available Slots Proceed
if (_AvailableConcurrencySlots > 0)
{
List <AdsGoFast.TaskMetaData.TaskGroup> _TaskGroups = TaskGroupsStatic.GetActive();
if (_TaskGroups.Count > 0)
{
short _ConcurrencySlotsAllocated = 0;
short _DefaultTasksPerGroup = 0;
short _DistributionLoopCounter = 1;
//Distribute Concurrency Slots
while (_AvailableConcurrencySlots > 0)
{
DistributeConcurrencySlots(ref _TaskGroups, ref _DefaultTasksPerGroup, ref _ConcurrencySlotsAllocated, ref _AvailableConcurrencySlots, _DistributionLoopCounter);
_DistributionLoopCounter += 1;
}
Table TempTarget = new Table
{
Schema = "dbo",
Name = "#TempGroups" + logging.DefaultActivityLogItem.ExecutionUid.ToString()
};
SqlConnection _con = TMD.GetSqlConnection();
TMD.BulkInsert(_TaskGroups.ToDataTable(), TempTarget, true, _con);
Dictionary <string, string> _params = new Dictionary <string, string>
{
{ "TempTable", TempTarget.QuotedSchemaAndName() }
};
string _sql = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "UpdateTaskInstancesWithTaskRunner", _params);
TMD.ExecuteSql(_sql, _con);
}
}
return(new { });
}
public void AutoBulkInsertAndMerge(DataTable dt, string StagingTableName, string TargetTableName)
{
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using (SqlConnection conn = TMD.GetSqlConnection())
{
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = null,
PersistedCon = conn
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = "dbo",
PersistedCon = conn
};
TargetTable.GetColumnsFromExistingDB(true);
TMD.BulkInsert(dt, SourceTable, true, conn);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.QuotedSchemaAndName() },
{ "SourceFullName", SourceTable.QuotedSchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams);
conn.Execute(MergeSQL);
}
}
public static JObject GetSQLMergeStatementCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
dynamic data = JsonConvert.DeserializeObject(requestBody);
JObject Root = new JObject();
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using (SqlConnection _con = TMD.GetSqlConnection())
{
string _token = Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken("https://database.windows.net/");
String g = Guid.NewGuid().ToString().Replace("-", "");
_con.AccessToken = _token;
JArray arrStage = (JArray)data["Stage"];
string _StagingTableSchema = data["StagingTableSchema"].ToString();
string _StagingTableName = "#Temp_" + data["StagingTableName"].ToString() + g.ToString();
string _CreateStatementStage = GenerateSQLStatementTemplates.GetCreateTable(arrStage, _StagingTableSchema, _StagingTableName, false);
TMD.ExecuteSql(_CreateStatementStage, _con);
JArray arrTarget = (JArray)data["Target"];
string _TargetTableSchema = data["TargetTableSchema"].ToString();
string _TargetTableName = "#Temp_" + data["TargetTableName"].ToString() + g.ToString();
string _CreateStatementTarget = GenerateSQLStatementTemplates.GetCreateTable(arrTarget, _TargetTableSchema, _TargetTableName, false);
TMD.ExecuteSql(_CreateStatementTarget, _con);
string _MergeStatement = TMD.GenerateMergeSQL(_StagingTableSchema, _StagingTableName, _TargetTableSchema, _TargetTableName, _con, true, logging);
string fullStagingTableName = string.Format("[{0}].[{1}]", _StagingTableSchema, _StagingTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
string fullTargetTableName = string.Format("[{0}].[{1}]", _TargetTableSchema, _TargetTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
_MergeStatement = _MergeStatement.Replace(_TargetTableName, fullTargetTableName);
_MergeStatement = _MergeStatement.Replace(_StagingTableName, fullStagingTableName);
//Add Select for ADF Lookup Activity
_MergeStatement += Environment.NewLine + "Select 1 ";
Root["MergeStatement"] = _MergeStatement;
logging.LogInformation("GetSQLMergeStatement Function complete.");
}
return(Root);
}
public static JObject GetSQLCreateStatementFromSchemaCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
JObject data = JsonConvert.DeserializeObject <JObject>(requestBody);
string _CreateStatement;
JArray arr;
if (data["Data"] != null)
{
//Need to swap logic for parquet vs sql etc
arr = (JArray)data["Data"]["value"];
}
else if (data["SchemaFileName"] != null)
{
string _storageAccountName = data["StorageAccountName"].ToString();
string _storageAccountContainer = data["StorageAccountContainer"].ToString();
string _relativePath = data["RelativePath"].ToString();
string _schemaFileName = data["SchemaFileName"].ToString();
_storageAccountName = _storageAccountName.Replace(".dfs.core.windows.net", "").Replace("https://", "").Replace(".blob.core.windows.net", "");
TokenCredential StorageToken = new TokenCredential(Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken("https://" + _storageAccountName + ".blob.core.windows.net", Shared._ApplicationOptions.UseMSI));
arr = (JArray)JsonConvert.DeserializeObject(Shared.Azure.Storage.ReadFile(_storageAccountName, _storageAccountContainer, _relativePath, _schemaFileName, StorageToken));
}
else
{
throw new System.ArgumentException("Not Valid parameters to GetSQLCreateStatementFromSchema Function!");
}
bool _DropIfExist = data["DropIfExist"] == null ? false : (bool)data["DropIfExist"];
_CreateStatement = GenerateSQLStatementTemplates.GetCreateTable(arr, data["TableSchema"].ToString(), data["TableName"].ToString(), _DropIfExist);
_CreateStatement += Environment.NewLine + "Select 1";
JObject Root = new JObject
{
["CreateStatement"] = _CreateStatement
};
return(Root);
}
public string GenerateInsertSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con)
{
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = StagingTableSchema,
PersistedCon = _con
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = TargetTableSchema,
PersistedCon = _con
};
TargetTable.GetColumnsFromExistingDB(true);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.SchemaAndName() },
{ "SourceFullName", SourceTable.SchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericInsert", SqlParams);
return(MergeSQL);
}
private static dynamic GenerateTaskMastersCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
JObject data = JsonConvert.DeserializeObject <JObject>(requestBody);
JArray tables = JArray.Parse(data["TableList"].ToString());
JObject jsontemplate = JObject.Parse(data["JsonTemplate"].ToString());
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("TaskMasterName", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("TaskTypeId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("TaskGroupId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("ScheduleMasterId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("SourceSystemId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("TargetSystemId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("DegreeOfCopyParallelism", Type.GetType("System.Int16")));
dt.Columns.Add(new DataColumn("AllowMultipleActiveInstances", Type.GetType("System.Boolean")));
dt.Columns.Add(new DataColumn("TaskDatafactoryIR", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("ActiveYN", Type.GetType("System.Boolean")));
dt.Columns.Add(new DataColumn("DependencyChainTag", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("DataFactoryId", Type.GetType("System.Int64")));
dt.Columns.Add(new DataColumn("TaskMasterJSON", Type.GetType("System.String")));
foreach (JToken t in tables)
{
DataRow dr = dt.NewRow();
dr["TaskMasterName"] = jsontemplate["TaskMasterName"].ToString().Replace("{@TableSchema@}", t["TABLE_SCHEMA"].ToString()).Replace("{@TableName@}", t["TABLE_NAME"].ToString());
dr["TaskTypeId"] = jsontemplate["TaskTypeId"];
dr["TaskGroupId"] = jsontemplate["TaskGroupId"];
dr["ScheduleMasterId"] = jsontemplate["ScheduleMasterId"];
dr["SourceSystemId"] = jsontemplate["SourceSystemId"];
dr["TargetSystemId"] = jsontemplate["TargetSystemId"];
dr["DegreeOfCopyParallelism"] = jsontemplate["DegreeOfCopyParallelism"];
dr["AllowMultipleActiveInstances"] = jsontemplate["AllowMultipleActiveInstances"];
dr["TaskDatafactoryIR"] = jsontemplate["TaskDatafactoryIR"];
dr["ActiveYN"] = jsontemplate["ActiveYN"];
dr["DependencyChainTag"] = jsontemplate["DependencyChainTag"].ToString().Replace("{@TableSchema@}", t["TABLE_SCHEMA"].ToString()).Replace("{@TableName@}", t["TABLE_NAME"].ToString());
dr["DataFactoryId"] = jsontemplate["DataFactoryId"];
dr["TaskDatafactoryIR"] = jsontemplate["TaskDatafactoryIR"];
JObject _TaskMasterJSON = new JObject
{
["Source"] = JObject.Parse(Shared.JsonHelpers.GetStringValueFromJSON(logging, "Source", jsontemplate, null, true).Replace("{@TableSchema@}", t["TABLE_SCHEMA"].ToString()).Replace("{@TableName@}", t["TABLE_NAME"].ToString())),
["Target"] = JObject.Parse(Shared.JsonHelpers.GetStringValueFromJSON(logging, "Target", jsontemplate, null, true).Replace("{@TableSchema@}", t["TABLE_SCHEMA"].ToString()).Replace("{@TableName@}", t["TABLE_NAME"].ToString()))
};
dr["TaskMasterJSON"] = JObject.Parse(_TaskMasterJSON.ToString());
dt.Rows.Add(dr);
}
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
string TempTableName = "#TempTaskMaster" + Guid.NewGuid().ToString();
Table TempTable = new Table
{
Name = TempTableName
};
SqlConnection _con = TMD.GetSqlConnection();
TMD.BulkInsert(dt, TempTable, true, _con);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTableName", TempTableName }
};
string _sql = GenerateSQLStatementTemplates.GetSQL(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.SQLTemplateLocation), "GenerateTaskMasters", SqlParams);
TMD.ExecuteSql(_sql, _con);
return(new { }); //Return Empty Object
}
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();
}
public static dynamic GetAzureStorageListingsCore(HttpRequest req, Logging logging)
{
string requestBody = new System.IO.StreamReader(req.Body).ReadToEndAsync().Result;
dynamic taskInformation = JsonConvert.DeserializeObject(requestBody);
string _TaskInstanceId = taskInformation["TaskInstanceId"].ToString();
string _ExecutionUid = taskInformation["ExecutionUid"].ToString();
try
{
string _storageAccountName = taskInformation["Source"]["StorageAccountName"];
//The name is actually the base url so we need to parse it to get the name only
_storageAccountName = _storageAccountName.Split('.')[0].Replace("https://", "");
string _storageAccountToken = taskInformation["Source"]["StorageAccountToken"];
Int64 _SourceSystemId = taskInformation["Source"]["SystemId"];
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _con = TMD.GetSqlConnection();
var res = _con.QueryWithRetry(string.Format("Select Max(PartitionKey) MaxPartitionKey from AzureStorageListing where SystemId = {0}", _SourceSystemId.ToString()));
string MaxPartitionKey = DateTime.UtcNow.AddDays(-1).ToString("yyyy-MM-dd hh:mm");
foreach (var r in res)
{
if (r.MaxPartitionKey != null)
{
MaxPartitionKey = DateTime.Parse(r.MaxPartitionKey).AddMinutes(-1).ToString("yyyy-MM-dd hh:mm");
}
}
using (HttpClient SourceClient = new HttpClient())
{
//Now use the SAS URI to connect rather than the MSI / Service Principal as AD Based Auth not yet avail for tables
var _storageCredentials = new StorageCredentials(_storageAccountToken);
var SourceStorageAccount = new CloudStorageAccount(storageCredentials: _storageCredentials, accountName: _storageAccountName, endpointSuffix: "core.windows.net", useHttps: true);
var client = SourceStorageAccount.CreateCloudTableClient();
CloudTable table = client.GetTableReference("Filelist");
TableQuery <DynamicTableEntity> query = new TableQuery <DynamicTableEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThan, MaxPartitionKey.ToString()));
DataTable dt = new DataTable();
DataColumn dc = new DataColumn();
dc.ColumnName = "PartitionKey";
dc.DataType = typeof(string);
dt.Columns.Add(dc);
DataColumn dc1 = new DataColumn();
dc1.ColumnName = "RowKey";
dc1.DataType = typeof(string);
dt.Columns.Add(dc1);
DataColumn dc2 = new DataColumn();
dc2.ColumnName = "SystemId";
dc2.DataType = typeof(Int64);
dt.Columns.Add(dc2);
DataColumn dc3 = new DataColumn();
dc3.ColumnName = "FilePath";
dc3.DataType = typeof(string);
dt.Columns.Add(dc3);
string Filelist = "";
TableContinuationToken token = null;
do
{
TableQuerySegment <DynamicTableEntity> resultSegment = table.ExecuteQuerySegmentedAsync(query, token).Result;
token = resultSegment.ContinuationToken;
//load into data table
foreach (var entity in resultSegment.Results)
{
DataRow dr = dt.NewRow();
dr["PartitionKey"] = entity.PartitionKey;
dr["RowKey"] = entity.RowKey;
dr["SystemId"] = _SourceSystemId;
dr["FilePath"] = entity.Properties["FilePath"].StringValue;
dt.Rows.Add(dr);
Filelist += entity.Properties["FilePath"].StringValue + System.Environment.NewLine;
}
} while (token != null);
if (dt.Rows.Count > 0)
{
Table t = new Table();
t.Schema = "dbo";
string TableGuid = Guid.NewGuid().ToString();
t.Name = $"#AzureStorageListing{TableGuid}";
TMD.BulkInsert(dt, t, true, _con);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTable", t.QuotedSchemaAndName() },
{ "SourceSystemId", _SourceSystemId.ToString() }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "MergeIntoAzureStorageListing", SqlParams);
_con.ExecuteWithRetry(MergeSQL, 120);
if ((JArray)taskInformation["Alerts"] != null)
{
foreach (JObject Alert in (JArray)taskInformation["Alerts"])
{
//Only Send out for Operator Level Alerts
if (Alert["AlertCategory"].ToString() == "Task Specific Operator Alert")
{
AlertOperator(_SourceSystemId, Alert["AlertEmail"].ToString(), "", Filelist);
}
}
}
}
_con.Close();
_con.Dispose();
TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.Complete, Guid.Empty, "");
}
}
catch (Exception e)
{
logging.LogErrors(e);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.FailedRetry, Guid.Empty, "Failed when trying to Generate Sas URI and Send Email");
JObject Root = new JObject
{
["Result"] = "Failed"
};
return(Root);
}
return(new { });
}
public string GenerateMergeSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con, bool CheckSchemaDrift, Logging logging)
{
if (CheckSchemaDrift)
{
SqlCommand cmd = new SqlCommand(string.Format("Select * from {0}.{1}", StagingTableSchema, StagingTableName), _con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable stagingDT = new DataTable();
da.Fill(stagingDT);
cmd = new SqlCommand(string.Format("Select * from {0}.{1}", TargetTableSchema, TargetTableName), _con);
da = new SqlDataAdapter(cmd);
DataTable targetDT = new DataTable();
da.Fill(targetDT);
bool schemaEqual = DataTableSchemaCompare.SchemaEquals(stagingDT, targetDT);
if (schemaEqual == false)
{
logging.LogWarning(string.Format("****Schema Drift for Table {0}.{1} to {2}.{3}", StagingTableSchema, StagingTableName.Replace("#Temp_", ""), TargetTableSchema, TargetTableName.Replace("#Temp_", "")));
}
}
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = StagingTableSchema,
PersistedCon = _con
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = TargetTableSchema,
PersistedCon = _con
};
TargetTable.GetColumnsFromExistingDB(true);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.SchemaAndName() },
{ "SourceFullName", SourceTable.SchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = string.Empty;
if (PrimaryKeyJoin.Length >= 4)
{
MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams);
}
else
{
MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericTruncateInsert", SqlParams);
}
return(MergeSQL);
}
public dynamic GetADFActivityRuns(Logging logging)
{
using var client = _logAnalyticsContext.httpClient.CreateClient(_logAnalyticsContext.httpClientName);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _conRead = TMD.GetSqlConnection();
//Get Last Request Date
var MaxTimesGen = _conRead.QueryWithRetry(@"
Select a.*, MaxActivityTimeGenerated from
DataFactory a left join
( Select b.DataFactoryId, MaxActivityTimeGenerated = Max(MaxActivityTimeGenerated)
from ADFActivityRun b
group by b.DatafactoryId) b on a.Id = b.DatafactoryId
");
DateTimeOffset MaxActivityTimeGenerated = DateTimeOffset.UtcNow.AddDays(-30);
foreach (var datafactory in MaxTimesGen)
{
if (datafactory.MaxActivityTimeGenerated != null)
{
MaxActivityTimeGenerated = ((DateTimeOffset)datafactory.MaxActivityTimeGenerated).AddMinutes(-5);
}
string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString();
Dictionary <string, object> KqlParams = new Dictionary <string, object>
{
{ "MaxActivityTimeGenerated", MaxActivityTimeGenerated.ToString("yyyy-MM-dd HH:mm:ss.ff K") },
{ "SubscriptionId", ((string)datafactory.SubscriptionUid.ToString()).ToUpper() },
{ "ResourceGroupName", ((string)datafactory.ResourceGroup.ToString()).ToUpper() },
{ "DataFactoryName", ((string)datafactory.Name.ToString()).ToUpper() },
{ "DatafactoryId", datafactory.Id.ToString() }
};
//Add in the rates from ADFServiceRates.json
string ADFRatesStr = System.IO.File.ReadAllText(Path.Combine(Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.KQLTemplateLocation), "ADFServiceRates.json"));
JObject ADFRates = JObject.Parse(ADFRatesStr);
foreach (JProperty p in ADFRates.Properties())
{
KqlParams.Add(p.Name, p.Value.ToString());
}
string KQL = System.IO.File.ReadAllText(Path.Combine(Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.KQLTemplateLocation), "GetADFActivityRuns.kql"));
KQL = KQL.FormatWith(KqlParams, FormatWith.MissingKeyBehaviour.ThrowException, null, '{', '}');
JObject JsonContent = new JObject();
JsonContent["query"] = KQL;
var postContent = new StringContent(JsonContent.ToString(), System.Text.Encoding.UTF8, "application/json");
var response = client.PostAsync($"https://api.loganalytics.io/v1/workspaces/{workspaceId}/query", postContent).Result;
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
//Start to parse the response content
HttpContent responseContent = response.Content;
var content = response.Content.ReadAsStringAsync().Result;
var tables = ((JArray)(JObject.Parse(content)["tables"]));
if (tables.Count > 0)
{
DataTable dt = new DataTable();
var rows = (JArray)(tables[0]["rows"]);
var columns = (JArray)(tables[0]["columns"]);
foreach (JObject c in columns)
{
DataColumn dc = new DataColumn();
dc.ColumnName = c["name"].ToString();
dc.DataType = GetADFStats.KustoDataTypeMapper[c["type"].ToString()];
dt.Columns.Add(dc);
}
foreach (JArray r in rows)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
dr[i] = ((Newtonsoft.Json.Linq.JValue)r[i]).Value;
}
dt.Rows.Add(dr);
}
Table t = new Table();
t.Schema = "dbo";
string TableGuid = Guid.NewGuid().ToString();
t.Name = $"#ADFActivityRun{TableGuid}";
using (SqlConnection _conWrite = TMD.GetSqlConnection())
{
TMD.BulkInsert(dt, t, true, _conWrite);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTable", t.QuotedSchemaAndName() },
{ "DatafactoryId", datafactory.Id.ToString() }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.SQLTemplateLocation), "MergeIntoADFActivityRun", SqlParams);
_conWrite.ExecuteWithRetry(MergeSQL, 120);
_conWrite.Close();
_conWrite.Dispose();
}
}
else
{
logging.LogErrors(new Exception("Kusto query failed getting ADFPipeline Stats."));
}
}
}
return(new { });
}
public static dynamic GetADFActivityErrors(Logging logging)
{
using var client = new HttpClient();
string token = Shared.Azure.AzureSDK.GetAzureRestApiToken("https://api.loganalytics.io");
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _conRead = TMD.GetSqlConnection();
//Get Last Request Date
//ToDo Add DataFactoryId field to ADFActivityErrors
var MaxTimesGen = _conRead.QueryWithRetry(@"
Select a.*, MaxTimeGenerated MaxTimeGenerated from
Datafactory a left join
( Select DataFactoryId, MaxTimeGenerated = Max(TimeGenerated)
from ADFActivityErrors b
group by DataFactoryId
) b on a.Id = b.DatafactoryId
");
DateTimeOffset MaxTimeGenerated = DateTimeOffset.UtcNow.AddDays(-30);
foreach (var datafactory in MaxTimesGen)
{
if (datafactory.MaxTimeGenerated != null)
{
MaxTimeGenerated = ((DateTimeOffset)datafactory.MaxTimeGenerated).AddMinutes(-5);
}
string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString();
Dictionary <string, object> KqlParams = new Dictionary <string, object>
{
{ "MaxActivityTimeGenerated", MaxTimeGenerated.ToString("yyyy-MM-dd HH:mm:ss.ff K") },
{ "SubscriptionId", ((string)datafactory.SubscriptionUid.ToString()).ToUpper() },
{ "ResourceGroupName", ((string)datafactory.ResourceGroup.ToString()).ToUpper() },
{ "DataFactoryName", ((string)datafactory.Name.ToString()).ToUpper() },
{ "DatafactoryId", datafactory.Id.ToString() }
};
string KQL = System.IO.File.ReadAllText(Shared.GlobalConfigs.GetStringConfig("KQLTemplateLocation") + "GetADFActivityErrors.kql");
KQL = KQL.FormatWith(KqlParams, FormatWith.MissingKeyBehaviour.ThrowException, null, '{', '}');
JObject JsonContent = new JObject();
JsonContent["query"] = KQL;
var postContent = new StringContent(JsonContent.ToString(), System.Text.Encoding.UTF8, "application/json");
var response = client.PostAsync($"https://api.loganalytics.io/v1/workspaces/{workspaceId}/query", postContent).Result;
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
//Start to parse the response content
HttpContent responseContent = response.Content;
var content = response.Content.ReadAsStringAsync().Result;
var tables = ((JArray)(JObject.Parse(content)["tables"]));
if (tables.Count > 0)
{
DataTable dt = new DataTable();
var rows = (JArray)(tables[0]["rows"]);
var columns = (JArray)(tables[0]["columns"]);
foreach (JObject c in columns)
{
DataColumn dc = new DataColumn();
dc.ColumnName = c["name"].ToString();
dc.DataType = KustoDataTypeMapper[c["type"].ToString()];
dt.Columns.Add(dc);
}
foreach (JArray r in rows)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
if (((Newtonsoft.Json.Linq.JValue)r[i]).Value != null)
{
dr[i] = ((Newtonsoft.Json.Linq.JValue)r[i]).Value;
}
else
{
dr[i] = DBNull.Value;
}
}
dt.Rows.Add(dr);
}
Table t = new Table();
t.Schema = "dbo";
string TableGuid = Guid.NewGuid().ToString();
t.Name = $"#ADFActivityErrors{TableGuid}";
using (SqlConnection _conWrite = TMD.GetSqlConnection())
{
TMD.BulkInsert(dt, t, true, _conWrite);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTable", t.QuotedSchemaAndName() },
{ "DatafactoryId", datafactory.Id.ToString() }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "MergeIntoADFActivityErrors", SqlParams);
_conWrite.ExecuteWithRetry(MergeSQL);
_conWrite.Close();
_conWrite.Dispose();
}
}
else
{
logging.LogErrors(new Exception("Kusto query failed getting ADFPipeline Stats."));
}
}
}
return(new { });
}
public dynamic GetActivityLevelLogsCore(Logging logging)
{
string AppInsightsWorkspaceId = _appOptions.Value.ServiceConnections.AppInsightsWorkspaceId;
using var client = _appInsightsContext.httpClient.CreateClient(_appInsightsContext.httpClientName);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _conRead = TMD.GetSqlConnection();
//Get Last Request Date
var MaxTimesGenQuery = _conRead.QueryWithRetry(@"
select max([timestamp]) maxtimestamp from ActivityLevelLogs");
foreach (var datafactory in MaxTimesGenQuery)
{
DateTimeOffset MaxAllowedLogTimeGenerated = DateTimeOffset.UtcNow.AddDays(-1 * _appOptions.Value.ServiceConnections.AppInsightsMaxNumberOfDaysToRequest);
DateTimeOffset MaxObservedLogTimeGenerated = DateTimeOffset.UtcNow.AddDays(-1 * _appOptions.Value.ServiceConnections.AppInsightsMaxNumberOfDaysToRequest);
if (datafactory.maxtimestamp != null)
{
MaxObservedLogTimeGenerated = ((DateTimeOffset)datafactory.maxtimestamp).AddMinutes(-1 * _appOptions.Value.ServiceConnections.AppInsightsMinutesOverlap);
//Make sure that we don't get more than max to ensure we dont get timeouts etc.
if ((MaxObservedLogTimeGenerated) <= MaxAllowedLogTimeGenerated)
{
MaxObservedLogTimeGenerated = MaxAllowedLogTimeGenerated;
}
}
//string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString();
Dictionary <string, object> KqlParams = new Dictionary <string, object>
{
{ "MaxLogTimeGenerated", MaxObservedLogTimeGenerated.ToString("yyyy-MM-dd HH:mm:ss.ff K") }
//{"SubscriptionId", ((string)datafactory.SubscriptionUid.ToString()).ToUpper()},
//{"ResourceGroupName", ((string)datafactory.ResourceGroup.ToString()).ToUpper() },
//{"DataFactoryName", ((string)datafactory.Name.ToString()).ToUpper() },
//{"DatafactoryId", datafactory.Id.ToString() }
};
string KQL = System.IO.File.ReadAllText(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.KQLTemplateLocation, "GetActivityLevelLogs.kql"));
KQL = KQL.FormatWith(KqlParams, FormatWith.MissingKeyBehaviour.ThrowException, null, '{', '}');
JObject JsonContent = new JObject();
JsonContent["query"] = KQL;
var postContent = new StringContent(JsonContent.ToString(), System.Text.Encoding.UTF8, "application/json");
var response = client.PostAsync($"https://api.applicationinsights.io/v1/apps/{AppInsightsWorkspaceId}/query", postContent).Result;
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
//Start to parse the response content
HttpContent responseContent = response.Content;
var content = response.Content.ReadAsStringAsync().Result;
var tables = ((JArray)(JObject.Parse(content)["tables"]));
if (tables.Count > 0)
{
DataTable dt = new DataTable();
var rows = (JArray)(tables[0]["rows"]);
var columns = (JArray)(tables[0]["columns"]);
foreach (JObject c in columns)
{
DataColumn dc = new DataColumn();
dc.ColumnName = c["name"].ToString();
dc.DataType = KustoDataTypeMapper[c["type"].ToString()];
dt.Columns.Add(dc);
}
foreach (JArray r in rows)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
if (((Newtonsoft.Json.Linq.JValue)r[i]).Value != null)
{
dr[i] = ((Newtonsoft.Json.Linq.JValue)r[i]).Value;
}
else
{
dr[i] = DBNull.Value;
}
}
dt.Rows.Add(dr);
}
Table t = new Table();
t.Schema = "dbo";
string TableGuid = Guid.NewGuid().ToString();
t.Name = "#ActivityLevelLogs{TableGuid}";
using (SqlConnection _conWrite = TMD.GetSqlConnection())
{
TMD.BulkInsert(dt, t, true, _conWrite);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTable", t.QuotedSchemaAndName() },
{ "DatafactoryId", "1" }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.SQLTemplateLocation), "MergeIntoActivityLevelLogs", SqlParams);
logging.LogInformation(MergeSQL.ToString());
_conWrite.ExecuteWithRetry(MergeSQL);
_conWrite.Close();
_conWrite.Dispose();
}
}
else
{
logging.LogErrors(new Exception("Kusto query failed getting ADFPipeline Stats."));
}
}
}
return(new { });
}
