Ejemplo n.º 1
0
        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
        }
Ejemplo n.º 7
0
        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();
        }
Ejemplo n.º 8
0
        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 { });
        }