{ "name": "PADailyAggregation_Jan1", "properties": { "activities": [{ "name": "RemoveDuplicates", "type": "SqlServerStoredProcedure", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[DailyDuplicatesRemove]", "storedProcedureParameters": { "startdate": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "AddRosu", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "DeviceStatistics", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[GetROSUNumber]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "DeviceStatistics", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "RemoveDuplicates", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[GetDeviceUsageStatistics]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "eventsUser", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "AddRosu", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[GetEventByUsers]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "DailyStatistics", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "eventsUser", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[AddNewStatistics_User]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "UpdateStatistics", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "DailyStatistics", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[UpdateStatistics_User]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "BackUpMonthlyMasterMain", "type": "Copy", "dependsOn": [{ "activity": "UpdatePharmacy", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlSource", "partitionOption": "None" }, "sink": { "type": "BlobSink" }, "enableStaging": false }, "inputs": [{ "referenceName": "MonthlyMainMaster", "type": "DatasetReference" }], "outputs": [{ "referenceName": "BackupMonthlyMainTable", "type": "DatasetReference" }] }, { "name": "NewReturningUsers", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "AddIamguidtable", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[GetNewAndReturningUsers]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "BackUpMonthly", "type": "Copy", "dependsOn": [{ "activity": "Monthlyupdatecomplete", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlSource", "partitionOption": "None" }, "sink": { "type": "BlobSink" }, "enableStaging": false }, "inputs": [{ "referenceName": "MonthlyStasticsUsers", "type": "DatasetReference" }], "outputs": [{ "referenceName": "BackupMonthlyTable", "type": "DatasetReference" }] }, { "name": "Monthlyaddcomplete", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "BackUpMonthlyMasterMain", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 3, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[AddNewStatistics_User_Complete]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "Monthlyupdatecomplete", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "Monthlyaddcomplete", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 3, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[UpdateStatistics_User_Complete]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "MonthlyStatisticsevents", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "BackUpMonthly", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 3, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[Update_PA_Monthly_Statistics]" }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "Get Folder Path", "type": "GetMetadata", "dependsOn": [{ "activity": "MonthlyStatisticsevents", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "dataset": { "referenceName": "GetCareProvidersPostcodeBlob", "type": "DatasetReference" }, "fieldList": [ "exists" ] } }, { "name": "If Folder Exists", "type": "IfCondition", "dependsOn": [{ "activity": "Get Folder Path", "dependencyConditions": [ "Succeeded" ] }], "userProperties": [], "typeProperties": { "expression": { "value": "@equals(activity('Get Folder Path').output.exists, true)", "type": "Expression" }, "ifTrueActivities": [{ "name": "Copy Blob to SQL", "type": "Copy", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "BlobSource", "recursive": true }, "sink": { "type": "AzureSqlSink" }, "enableStaging": false, "translator": { "type": "TabularTranslator", "mappings": [{ "source": { "name": "Prop_0", "type": "Int32" }, "sink": { "name": "GetCareProvidersID", "type": "Int32" } }, { "source": { "name": "Prop_1", "type": "Datetime" }, "sink": { "name": "EventDate", "type": "Datetime" } }, { "source": { "name": "Prop_2", "type": "String" }, "sink": { "name": "PostCode", "type": "String" } }, { "source": { "name": "Prop_3", "type": "String" }, "sink": { "name": "OutCode", "type": "String" } } ] } }, "inputs": [{ "referenceName": "GetCareProvidersPostcodeBlob", "type": "DatasetReference" }], "outputs": [{ "referenceName": "GetCareProvidersPostcodeSQL", "type": "DatasetReference" }] }, { "name": "Remove Duplicate Post Codes", "type": "Lookup", "dependsOn": [{ "activity": "Copy Blob to SQL", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "AzureSqlSource", "sqlReaderQuery": { "value": "WITH CTE AS(\n SELECT GetCareProvidersID, EventDate,\n RN = ROW_NUMBER()OVER(PARTITION BY GetCareProvidersID ORDER BY GetCareProvidersID)\n FROM [dbo].[GetCareproviderPostcode]\n WHERE CAST(EventDate as DATE) = '@{pipeline().parameters.windowStart}'\n)\nDELETE FROM CTE WHERE RN > 1;\n\nSELECT 0;", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "RemovePostcodesSQL", "type": "DatasetReference" }, "firstRowOnly": false } }, { "name": "Trigger AggregatePostcode", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "Remove Duplicate Post Codes", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[AggregatePostcodes]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "Datetime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "Copy PostCode Data to Blob", "type": "Copy", "dependsOn": [{ "activity": "Trigger AggregatePostcode", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "AzureSqlSource", "sqlReaderQuery": { "value": "SELECT * FROM AggregatedPostcodes", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "sink": { "type": "BlobSink" }, "enableStaging": false }, "inputs": [{ "referenceName": "PostcodeTable", "type": "DatasetReference" }], "outputs": [{ "referenceName": "PostcodeBlob", "type": "DatasetReference" }] } ] } }, { "name": "AddIamguidtable", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "If Folder Exists", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[Mapping_IAMguidTable]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "Addpatientidtable", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "NewReturningUsers", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[AddPatientId]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "AddLinkedPId", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "Addpatientidtable", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[AddLinkedPid]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "AlterIndex", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "AutoDelete", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[alterindex]" }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "AutoDelete", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "AddLinkedPId", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[autoDelete]", "storedProcedureParameters": { "startdate": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "UpdatePharmacy", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "UpdateStatistics", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[Update_Pharmacy_ID]", "storedProcedureParameters": { "FromDateTime": { "value": { "value": "@pipeline().parameters.windowStart", "type": "Expression" }, "type": "DateTime" } } }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } }, { "name": "Backup Users By Practice", "type": "SqlServerStoredProcedure", "dependsOn": [{ "activity": "AlterIndex", "dependencyConditions": [ "Succeeded" ] }], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "storedProcedureName": "[dbo].[UsersByPractice]" }, "linkedServiceName": { "referenceName": "patientaccess_sqlserver", "type": "LinkedServiceReference" } } ], "parameters": { "windowStart": { "type": "String" } }, "annotations": [], "lastPublishTime": "2022-02-03T06:05:26Z" }, "type": "Microsoft.DataFactory/factories/pipelines" }