{ "name": "Controle laatste wijziging", "properties": { "activities": [ { "name": "LaatsteWijzigingBrondatabase", "description": "De laatste wijzigdatum wordt elke dag opgehaald en opgeslagen. Hierdoor kan worden gecontroleerd of het terecht is dat een verdere refresh niet wordt opgestart.", "type": "Copy", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "OdbcSource", "query": "SELECT FIRST 1 \nGC_WIJZIGDATUM AS LaatsteWijzigdatum,\n(select cast('NOW' as timestamp) from rdb$database) AS Controletijdstip,\ndatediff (HOUR FROM GC_WIJZIGDATUM TO (select cast('NOW' as timestamp) from rdb$database)) AS ouderdomuren\n\nFROM AT_DOCUMENT\nORDER BY GC_WIJZIGDATUM desc\n", "queryTimeout": "02:00:00" }, "sink": { "type": "AzureSqlSink", "tableOption": "autoCreate", "disableMetricsCollection": false }, "enableStaging": false, "translator": { "type": "TabularTranslator", "mappings": [ { "source": { "name": "LAATSTEWIJZIGDATUM", "type": "DateTime" }, "sink": { "name": "LAATSTEWIJZIGDATUM", "type": "DateTime", "physicalType": "datetime" } }, { "source": { "name": "CONTROLETIJDSTIP", "type": "DateTime" }, "sink": { "name": "CONTROLETIJDSTIP", "type": "DateTime", "physicalType": "datetime" } }, { "source": { "name": "OUDERDOMUREN", "type": "Int64" }, "sink": { "name": "OUDERDOMUREN", "type": "Int64", "physicalType": "bigint" } } ], "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } }, "inputs": [ { "referenceName": "Firebirddatabase", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "ExtraOuderdomDatabase", "type": "DatasetReference" } ] }, { "name": "Controleer laatste wijziging", "description": "Laatste wijziging in Firebird Database moet minder van 24 uur oud zijn. ", "type": "IfCondition", "dependsOn": [ { "activity": "lookup_wijzigdatum", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "expression": { "value": "@lessOrEquals(activity('lookup_wijzigdatum').output.firstRow.OuderdomUren,pipeline().globalParameters.maximale_ouderdom)", "type": "Expression" }, "ifFalseActivities": [ { "name": "SendFailureEmail-database_niet_uptodate", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Brondatabase is niet recent of wijziging in de laatste @{pipeline().globalParameters.maximale_ouderdom} uur. Connectie op operationele database: @{pipeline().globalParameters.connectie_op_operationele_database} Klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } } ], "ifTrueActivities": [ { "name": "SendSuccessEmail_database_uptodate", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-17.westeurope.logic.azure.com:443/workflows/6505c39bf85742e08102856019692cbd/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=4RrfjEXs4W-dKn9irASYU15ihJH0-E_oKdTTtxu6ijA", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Brondatabase van @{pipeline().globalParameters.klant} is recent.", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_succes": "@pipeline().globalParameters.receiver_succes" } } }, { "name": "Execute Fase 1", "type": "ExecutePipeline", "dependsOn": [], "userProperties": [], "typeProperties": { "pipeline": { "referenceName": "1_Fase1", "type": "PipelineReference" }, "waitOnCompletion": true } }, { "name": "Execute Fase 2", "type": "ExecutePipeline", "dependsOn": [ { "activity": "Execute Fase 1", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "pipeline": { "referenceName": "2_Fase2", "type": "PipelineReference" }, "waitOnCompletion": true } }, { "name": "Execute Fase 3", "type": "ExecutePipeline", "dependsOn": [ { "activity": "Execute Fase 2", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "pipeline": { "referenceName": "3_Fase 3", "type": "PipelineReference" }, "waitOnCompletion": true } }, { "name": "SendFailureEmail-failure_fase1", "type": "WebActivity", "dependsOn": [ { "activity": "Execute Fase 1", "dependencyConditions": [ "Failed" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Fase1 is niet goed uitgevoerd. De ETL is gestopt. klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } }, { "name": "SendFailureEmail-failure_fase2", "type": "WebActivity", "dependsOn": [ { "activity": "Execute Fase 2", "dependencyConditions": [ "Failed" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Fase2 is niet goed uitgevoerd. De ETL is gestopt. klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } }, { "name": "Execute fase 4", "type": "ExecutePipeline", "dependsOn": [ { "activity": "Execute Fase 3", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "pipeline": { "referenceName": "4_fase 4", "type": "PipelineReference" }, "waitOnCompletion": true } }, { "name": "SendFailureEmail-failure_fase3", "type": "WebActivity", "dependsOn": [ { "activity": "Execute Fase 3", "dependencyConditions": [ "Failed" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Fase3 is niet goed uitgevoerd. klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } }, { "name": "SendFailureEmail-failure_fase4", "type": "WebActivity", "dependsOn": [ { "activity": "Execute fase 4", "dependencyConditions": [ "Failed" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Fase4 is niet goed uitgevoerd. klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } }, { "name": "Execute Fase 5", "type": "ExecutePipeline", "dependsOn": [ { "activity": "Execute fase 4", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "pipeline": { "referenceName": "5_Fase 5", "type": "PipelineReference" }, "waitOnCompletion": true } }, { "name": "SendFailureEmail-failure_fase5", "type": "WebActivity", "dependsOn": [ { "activity": "Execute Fase 5", "dependencyConditions": [ "Failed" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://prod-54.westeurope.logic.azure.com:443/workflows/e77f9ab6a3af4cf4bbdde6ed712cc1d5/triggers/manual/paths/invoke?api-version=2016-10-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=N88ytqJQnw8juI7_pWjYDT2o8GiS00HqHGvESDxSnp8", "connectVia": { "referenceName": "AzureIR-zonderVNET", "type": "IntegrationRuntimeReference" }, "method": "POST", "headers": { "Content-Type": "application/json" }, "body": { "message": "Fase5 is niet goed uitgevoerd. klant:@{pipeline().globalParameters.klant}", "dataFactoryName": "@{pipeline().DataFactory}", "pipelineName": "@{pipeline().Pipeline}", "receiver_fail": "@pipeline().globalParameters.receiver_fail" } } } ] } }, { "name": "lookup_wijzigdatum", "description": "De laatste wijzigdatum van de documenten wordt opgehaald. Als deze groter is dan <> dan wordt de refresh niet opgestart. Over het algemeen moet deze wijzigdatum <24uur zijn. >24uur betekent dat er niks is veranderd óf dat er met een verouderde database wordt gewerkt. Het heeft dan geen zin om het DWH te updaten.", "type": "Lookup", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "OdbcSource", "query": "SELECT FIRST 1 \nGC_WIJZIGDATUM AS LaatsteWijzigdatum,\n(select cast('NOW' as timestamp) from rdb$database) AS Controletijdstip,\ndatediff (HOUR FROM GC_WIJZIGDATUM TO (select cast('NOW' as timestamp) from rdb$database)) AS OuderdomUren\n\nFROM AT_DOCUMENT\nORDER BY GC_WIJZIGDATUM desc", "queryTimeout": "02:00:00" }, "dataset": { "referenceName": "Firebirddatabase", "type": "DatasetReference" } } } ], "folder": { "name": "Controle" }, "annotations": [] } }