/***** 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);
}
