/***** Process Source *****/ public void ProcessSourceSystem(SourceAndTargetSystem_JsonSchemas schemas) { bool Processed = false; JObject Source = new JObject { ["Type"] = this.SourceSystemType }; //Validate SourceSystemJson based on JSON Schema string sourcesystem_schema = schemas.GetBySystemType(this.SourceSystemType).JsonSchema; _TaskIsValid = Shared.JsonHelpers.ValidateJsonUsingSchema(logging, sourcesystem_schema, this.SourceSystemJSON, "Failed to validate SourceSystem JSON for System Type: " + this.SourceSystemType + ". "); if (SourceSystemType == "ADLS" || SourceSystemType == "Azure Blob" || SourceSystemType == "File") { ProcessSourceSystem_TypeIsStorage(ref Source); Processed = true; } if (SourceSystemType == "Azure SQL" || SourceSystemType == "SQL Server") { ProcessSourceSystem_TypeIsSQL(ref Source); Processed = true; } if (!Processed) { ProcessSourceSystem_Default(ref Source); } _JsonObjectForADF["Source"] = Source; }
/***** Process Target *****/ public void ProcessTargetSystem(SourceAndTargetSystem_JsonSchemas schemas) { JObject Target = new JObject { ["Type"] = this.TargetSystemType }; //Validate SourceSystemJson based on JSON Schema string targetsystem_schema = schemas.GetBySystemType(this.TargetSystemType).JsonSchema; _TaskIsValid = Shared.JsonHelpers.ValidateJsonUsingSchema(logging, targetsystem_schema, this.TargetSystemJSON, "Failed to validate TargetSystem JSON for System Type: " + this.TargetSystemId + ". "); if (TargetSystemType == "ADLS" || TargetSystemType == "Azure Blob" || TargetSystemType == "File") { ProcessTargetSystem_TypeIsStorage(ref Target); goto ProcessTargetSystemEnd; } if (TargetSystemType == "Azure SQL" || TargetSystemType == "SQL Server") { ProcessTargetSystem_TypeIsSQL(ref Target); goto ProcessTargetSystemEnd; } //Default Processing Branch Target.Merge(_TargetSystemJson, new JsonMergeSettings { // union array values together to avoid duplicates MergeArrayHandling = MergeArrayHandling.Union }); ProcessTargetSystemEnd: _JsonObjectForADF["Target"] = Target; }
//Processing Core ***/ public JObject ProcessRoot(TaskTypeMappings ttm, SourceAndTargetSystem_JsonSchemas system_schemas) { CreateInternalObjectsForProcessingJsonFields(); ProcessSourceSystem(system_schemas); ProcessTargetSystem(system_schemas); ProcessTaskInstance(ttm); ProcessTaskMaster(ttm); return(_JsonObjectForADF); }
public ADFJsonArray(Guid _ExecutionUid, short _TaskRunnerId, Logging _logging) { this.logging = _logging; this.ExecutionUid = _ExecutionUid; this.TaskRunnerId = _TaskRunnerId; TaskMetaDataDatabase TMD = new TaskMetaDataDatabase(); //Get All Task Instance Objects for the current Framework Task Runner this.TIs = TMD.GetSqlConnection().Query <AdsGoFast.GetTaskInstanceJSON.BaseTask>(string.Format("Exec [dbo].[GetTaskInstanceJSON] {0}", TaskRunnerId.ToString())); this.TIsJson = new JArray(); //Instantiate the Collections that contain the JSON Schemas using TaskTypeMappings ttm = new TaskTypeMappings(); using SourceAndTargetSystem_JsonSchemas system_schemas = new SourceAndTargetSystem_JsonSchemas(); //Set up table to Store Invalid Task Instance Objects this.InvalidTIs = new DataTable(); InvalidTIs.Columns.Add("ExecutionUid", System.Type.GetType("System.Guid")); InvalidTIs.Columns.Add("TaskInstanceId", System.Type.GetType("System.Int64")); InvalidTIs.Columns.Add("LastExecutionComment", System.Type.GetType("System.String")); foreach (AdsGoFast.GetTaskInstanceJSON.BaseTask TBase in TIs) { try { bool TaskIsValid = true; //Cast Raw Database Result into Base Class for ADF Json Creation ADFJsonBaseTask T = (ADFJsonBaseTask)(TBase); //T.logging = logging; //Set the base properties using data stored in non-json columns of the database T.CreateJsonObjectForADF(ExecutionUid); JObject Root = T.GetJsonObjectForADF(); T.CreateInternalObjectsForProcessingJsonFields(); T.ProcessSourceSystem(system_schemas); T.ProcessTargetSystem(system_schemas); T.ProcessTaskMaster(ttm); } catch (Exception e) { } } }
/// <summary> /// Returns the JSON object representation of TaskInstance as requried by Azure Data Factory /// </summary> /// <param name="log"></param> public static JArray GetActive_ADFJSON(Guid ExecutionUid, short TaskRunnerId, Logging logging) { TaskMetaDataDatabase TMD = new TaskMetaDataDatabase(); //Get All Task Instance Objects for the current Framework Task Runner IEnumerable <AdsGoFast.GetTaskInstanceJSON.BaseTask> TIs = TMD.GetSqlConnection().Query <AdsGoFast.GetTaskInstanceJSON.BaseTask>(string.Format("Exec [dbo].[GetTaskInstanceJSON] {0}, '{1}'", TaskRunnerId.ToString(), ExecutionUid.ToString())); JArray TIsJson = new JArray(); //Instantiate the Collections that contain the JSON Schemas using TaskTypeMappings ttm = new TaskTypeMappings(); using SourceAndTargetSystem_JsonSchemas system_schemas = new SourceAndTargetSystem_JsonSchemas(); //Set up table to Store Invalid Task Instance Objects DataTable InvalidTIs = new DataTable(); InvalidTIs.Columns.Add("ExecutionUid", System.Type.GetType("System.Guid")); InvalidTIs.Columns.Add("TaskInstanceId", System.Type.GetType("System.Int64")); InvalidTIs.Columns.Add("LastExecutionComment", System.Type.GetType("System.String")); foreach (AdsGoFast.GetTaskInstanceJSON.BaseTask TBase in TIs) { try { bool TaskIsValid = true; //Add Task Ids to Logging Object logging.DefaultActivityLogItem.TaskInstanceId = TBase.TaskInstanceId; logging.DefaultActivityLogItem.TaskMasterId = TBase.TaskMasterId; AdsGoFast.GetTaskInstanceJSON.ADFJsonBaseTask T = new GetTaskInstanceJSON.ADFJsonBaseTask(TBase, logging); //Set the base properties using data stored in non-json columns of the database T.CreateJsonObjectForADF(ExecutionUid); JObject Root = T.GetJsonObjectForADF(); if (T.ADFPipeline.StartsWith("AZ-Storage-Binary-AZ-Storage-Binary") || T.ADFPipeline.StartsWith("AZ-Storage-Cache-File-List") || T.ADFPipeline == "Cache-File-List-To-Email-Alert" || T.TaskExecutionType == "AF") /*|| T.ADFPipeline.Contains("SQL-AZ-Storage-Parquet") || T.ADFPipeline.Contains("AZ-SQL-StoredProcedure")*/ { Root = T.ProcessRoot(ttm, system_schemas); goto FinalTaskValidation; } //Create the Default JSON Objects for data stored in JSON fields in the database JObject TaskMasterJson = new JObject(); JObject TaskMasterJSONSource = new JObject(); if (Shared.JsonHelpers.IsValidJson(T.TaskMasterJson)) { TaskMasterJson = JObject.Parse(T.TaskMasterJson); if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Source", JObject.Parse(T.TaskMasterJson))) { TaskMasterJSONSource = (JObject)TaskMasterJson["Source"]; } } JObject TaskMasterJSONTarget = new JObject(); if (Shared.JsonHelpers.IsValidJson(T.TaskMasterJson)) { TaskMasterJson = JObject.Parse(T.TaskMasterJson); if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Target", JObject.Parse(T.TaskMasterJson))) { TaskMasterJSONTarget = (JObject)TaskMasterJson["Target"]; } } JObject TargetSystemJson = new JObject(); if (Shared.JsonHelpers.IsValidJson(T.TargetSystemJSON)) { TargetSystemJson = JObject.Parse(T.TargetSystemJSON); } JObject SourcesystemJson = new JObject(); if (Shared.JsonHelpers.IsValidJson(T.SourceSystemJSON)) { SourcesystemJson = JObject.Parse(T.SourceSystemJSON); } /**Start of branching based on specific Source Systems**/ JObject Source = new JObject { ["Type"] = T.SourceSystemType }; //Source of ADLS or BLOB if (T.SourceSystemType == "ADLS" || T.SourceSystemType == "Azure Blob" || T.SourceSystemType == "File") { //Properties on Source System Source["SystemId"] = T.SourceSystemId; Source["StorageAccountName"] = T.SourceSystemServer; Source["StorageAccountAccessMethod"] = T.SourceSystemAuthType; //Todo: Check if this is legacy if (Source["StorageAccountAccessMethod"].ToString() == "SASURI") { Source["StorageAccountSASUriKeyVaultSecretName"] = JObject.Parse(T.SourceSystemJSON)["SASUriKeyVaultSecretName"]; } Source["StorageAccountContainer"] = JObject.Parse(T.SourceSystemJSON)["Container"]; //Properties on Task Master if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Source", JObject.Parse(T.TaskMasterJson))) { Source["DataFileName"] = TaskMasterJSONSource["DataFileName"]; Source["SchemaFileName"] = TaskMasterJSONSource["SchemaFileName"]; Source["FirstRowAsHeader"] = TaskMasterJSONSource["FirstRowAsHeader"]; Source["SheetName"] = TaskMasterJSONSource["SheetName"]; Source["SkipLineCount"] = TaskMasterJSONSource["SkipLineCount"]; Source["MaxConcorrentConnections"] = TaskMasterJSONSource["MaxConcorrentConnections"]; Source["Recursively"] = TaskMasterJSONSource["Recursively"]; Source["DeleteAfterCompletion"] = TaskMasterJSONSource["DeleteAfterCompletion"]; Source["UserId"] = TaskMasterJSONSource["UserId"]; Source["Host"] = TaskMasterJSONSource["Host"]; Source["PasswordKeyVaultSecretName"] = TaskMasterJSONSource["PasswordKeyVaultSecretName"]; //Cache Azure Storage File List Task Type if (T.TaskType == "Cache Azure Storage File List") { Source["StorageAccountToken"] = TaskMasterJSONSource["StorageAccountToken"]; } //SAS Uri if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Type", TaskMasterJSONSource)) { if (TaskMasterJSONSource["Type"].Value <string>() == "SASUri") { Source["SasURIDaysValid"] = TaskMasterJSONSource["SasURIDaysValid"]; Source["TargetSystemUidInPHI"] = TaskMasterJSONSource["TargetSystemUidInPHI"]; Source["FileUploaderWebAppURL"] = TaskMasterJSONSource["FileUploaderWebAppURL"]; } } } //Properties on Task Instance if (T.TaskInstanceJson != "") { Source["RelativePath"] = Shared.JsonHelpers.GetStringValueFromJSON(logging, "SourceRelativePath", JObject.Parse(T.TaskInstanceJson), "", false); } } //End of ADLS, BLOB and File Branch //Source Azure SQL if (T.SourceSystemType == "Azure SQL" || T.SourceSystemType == "SQL Server") { JObject Database = new JObject { //Properties on Source System ["SystemName"] = T.SourceSystemServer, ["Name"] = JObject.Parse(T.SourceSystemJSON)["Database"], ["AuthenticationType"] = T.SourceSystemAuthType }; if (Database["AuthenticationType"].ToString() == "Username/Password") { Database["UsernameKeyVaultSecretName"] = JObject.Parse(T.SourceSystemJSON)["UsernameKeyVaultSecretName"]; Database["PasswordKeyVaultSecretName"] = JObject.Parse(T.SourceSystemJSON)["PasswordKeyVaultSecretName"]; } if (Database["AuthenticationType"].ToString() == "SQLAuth") { Database["Username"] = T.SourceSystemUserName; Database["PasswordKeyVaultSecretName"] = T.SourceSystemSecretName; } Source["Database"] = Database; JObject Extraction = new JObject { ["Type"] = JObject.Parse(T.TaskMasterJson)["Source"]["Type"], ["IncrementalType"] = IncrementalType(JObject.Parse(T.TaskMasterJson)), ["IncrementalField"] = JObject.Parse(T.TaskInstanceJson)["IncrementalField"], ["IncrementalColumnType"] = JObject.Parse(T.TaskInstanceJson)["IncrementalColumnType"] }; if (JObject.Parse(T.TaskInstanceJson)["IncrementalColumnType"] != null) { if (JObject.Parse(T.TaskInstanceJson)["IncrementalColumnType"].ToString() == "DateTime") { DateTime _IncrementalValue = (DateTime)JObject.Parse(T.TaskInstanceJson)["IncrementalValue"]; Extraction["IncrementalValue"] = _IncrementalValue.ToString("yyyy-MM-dd HH:mm:ss.fff");//JObject.Parse(T.TaskInstanceJson)["IncrementalValue"].ToString(); } else if (JObject.Parse(T.TaskInstanceJson)["IncrementalColumnType"].ToString() == "BigInt") { int _IncrementalValue = (int)JObject.Parse(T.TaskInstanceJson)["IncrementalValue"]; Extraction["IncrementalValue"] = _IncrementalValue.ToString();//JObject.Parse(T.TaskInstanceJson)["IncrementalValue"].ToString(); } } Extraction["ChunkField"] = JObject.Parse(T.TaskMasterJson)["Source"]["ChunkField"]; Extraction["ChunkSize"] = JObject.Parse(T.TaskMasterJson)["Source"]["ChunkSize"]; Extraction["TableSchema"] = JObject.Parse(T.TaskMasterJson)["Source"]["TableSchema"]; Extraction["TableName"] = JObject.Parse(T.TaskMasterJson)["Source"]["TableName"]; Extraction["SQLStatement"] = CreateSQLStatement(JObject.Parse(T.TaskMasterJson), JObject.Parse(T.TaskInstanceJson)); Source["Extraction"] = Extraction; JObject Execute = new JObject(); if (Shared.JsonHelpers.CheckForJSONProperty(logging, "StoredProcedure", TaskMasterJSONSource)) { string _storedProcedure = TaskMasterJSONSource["StoredProcedure"].ToString(); if (_storedProcedure.Length > 0) { string _spParameters = string.Empty; if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Parameters", TaskMasterJSONSource)) { _spParameters = TaskMasterJSONSource["Parameters"].ToString(); } _storedProcedure = string.Format("Exec {0} {1} {2} {3}", _storedProcedure, _spParameters, Environment.NewLine, " Select 1"); } Execute["StoredProcedure"] = _storedProcedure; } Source["Execute"] = Execute; } //End of SQL Source Branch Root["Source"] = Source; //Start of Target Logic Branch JObject Target = new JObject { ["Type"] = T.TargetSystemType }; //Send Grid if (T.TargetSystemType == "SendGrid") { Target["EmailRecipient"] = JObject.Parse(T.TaskMasterJson)["Target"]["Email Recipient"]; Target["EmailRecipientName"] = JObject.Parse(T.TaskMasterJson)["Target"]["Email Recipient Name"]; Target["EmailTemplateFileName"] = JObject.Parse(T.TaskMasterJson)["Target"]["Email Template File Name"]; Target["EmailSubject"] = JObject.Parse(T.TaskMasterJson)["Target"]["Email Subject"]; Target["SenderEmail"] = JObject.Parse(T.TargetSystemJSON)["SenderEmail"]; Target["SenderDescription"] = JObject.Parse(T.TargetSystemJSON)["SenderDescription"]; } //Target of ADLS or BLOB if (T.TargetSystemType == "ADLS" || T.TargetSystemType == "Azure Blob") { //Properties on Source System Target["StorageAccountName"] = T.TargetSystemServer; Target["StorageAccountContainer"] = JObject.Parse(T.TargetSystemJSON)["Container"]; Target["StorageAccountAccessMethod"] = T.TargetSystemAuthType; if (Target["StorageAccountAccessMethod"].ToString() == "SASURI") { Target["StorageAccountSASUriKeyVaultSecretName"] = JObject.Parse(T.TargetSystemJSON)["SASUriKeyVaultSecretName"]; } //Properties on Task Master if (T.TaskType == "Generate Task Masters") { Target["RelativePath"] = null; } else { Target["RelativePath"] = JObject.Parse(T.TaskInstanceJson)["TargetRelativePath"].ToString(); } Target["DataFileName"] = JObject.Parse(T.TaskMasterJson)["Target"]["DataFileName"]; Target["SchemaFileName"] = JObject.Parse(T.TaskMasterJson)["Target"]["SchemaFileName"]; Target["FirstRowAsHeader"] = JObject.Parse(T.TaskMasterJson)["Target"]["FirstRowAsHeader"]; Target["SheetName"] = JObject.Parse(T.TaskMasterJson)["Target"]["SheetName"]; Target["SkipLineCount"] = JObject.Parse(T.TaskMasterJson)["Target"]["SkipLineCount"]; Target["MaxConcorrentConnections"] = JObject.Parse(T.TaskMasterJson)["Target"]["MaxConcorrentConnections"]; } //Taget Azure SQL if (T.TargetSystemType == "Azure SQL") { JObject Database = new JObject { //Properties on Target System ["SystemName"] = T.TargetSystemServer, ["AuthenticationType"] = T.TargetSystemAuthType }; if (Shared.JsonHelpers.CheckForJSONProperty(logging, "Database", TargetSystemJson)) { Database["Name"] = JObject.Parse(T.TargetSystemJSON)["Database"]; if (Database["AuthenticationType"].ToString() == "Username/Password") { if (Shared.JsonHelpers.CheckForJSONProperty(logging, "UsernameKeyVaultSecretName", TargetSystemJson) && Shared.JsonHelpers.CheckForJSONProperty(logging, "PasswordKeyVaultSecretName", TargetSystemJson)) { Database["UsernameKeyVaultSecretName"] = TargetSystemJson["UsernameKeyVaultSecretName"]; Database["PasswordKeyVaultSecretName"] = TargetSystemJson["PasswordKeyVaultSecretName"]; } else { MarkTaskAsInvalid(logging, T.TargetSystemId, ExecutionUid, "Target System with id of '{0}' is configured incorrectly. Authentication Type is Username/Password but UsernameKeyVaultSecretName and PasswordKeyVaultSecretName are not properties in SystemJson.", ref TaskIsValid); } } } else { MarkTaskAsInvalid(logging, T.TargetSystemId, ExecutionUid, "Target System with id of '{0}' is configured incorrectly. The Database property is missing from SystemJson.", ref TaskIsValid); } Target["Database"] = Database; //Properties on Task Master if (TaskMasterJson.Properties().Count() > 0) { Shared.JsonHelpers.JsonObjectPropertyList pl_TargetBase = new Shared.JsonHelpers.JsonObjectPropertyList() { { "Type" } }; TaskIsValid = Shared.JsonHelpers.BuildJsonObjectWithValidation(logging, ref pl_TargetBase, TaskMasterJson["Target"].ToString(), ref Target); if (TaskIsValid) { if (Target["Type"].Value <string>() == "Table") { Shared.JsonHelpers.JsonObjectPropertyList pl_SQLTable = new Shared.JsonHelpers.JsonObjectPropertyList() { { "Type" }, { "TableSchema" }, { "TableName" }, { "StagingTableSchema" }, { "StagingTableName" }, { "AutoCreateTable" }, { "PreCopySQL" }, { "PostCopySQL" }, { "MergeSQL" }, { "AutoGenerateMerge" }, { "DynamicMapping", false, false }, }; TaskIsValid = Shared.JsonHelpers.BuildJsonObjectWithValidation(logging, ref pl_SQLTable, TaskMasterJson["Target"].ToString(), ref Target); } } } } Root["Target"] = Target; JObject DataFactory = new JObject { ["Id"] = T.DataFactoryId, ["Name"] = T.DataFactoryName, ["ResourceGroup"] = T.DataFactoryResourceGroup, ["SubscriptionId"] = T.DataFactorySubscriptionId, ["ADFPipeline"] = T.ADFPipeline }; Root["DataFactory"] = DataFactory; if (T.TaskType == "Generate Task Masters") { Root["TaskMasterTemplate"] = JObject.Parse(T.TaskMasterJson); } FinalTaskValidation: if (TaskIsValid) { TIsJson.Add(Root); } else { DataRow dr = InvalidTIs.NewRow(); dr["TaskInstanceId"] = T.TaskInstanceId; dr["ExecutionUid"] = ExecutionUid; dr["LastExecutionComment"] = "Task structure is invalid. Check activity level logs for details."; InvalidTIs.Rows.Add(dr); } } //End Try catch (Exception e) { logging.LogErrors(e); //ToDo: Convert to bulk insert TMD.LogTaskInstanceCompletion(TBase.TaskInstanceId, ExecutionUid, TaskStatus.FailedNoRetry, System.Guid.Empty, "Uncaught error building Task Instance JSON object."); DataRow dr = InvalidTIs.NewRow(); dr["TaskInstanceId"] = TBase.TaskInstanceId; dr["ExecutionUid"] = ExecutionUid; dr["LastExecutionComment"] = "Task structure is invalid. Check activity level logs for details."; InvalidTIs.Rows.Add(dr); } } // End For Each // foreach (DataRow dr in InvalidTIs.Rows) { //ToDo: Convert to bulk insert TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(dr["TaskInstanceId"]), ExecutionUid, TaskStatus.FailedNoRetry, System.Guid.Empty, dr["LastExecutionComment"].ToString()); } return(TIsJson); }