[Azure Data Factory] Parent Cases Flow Configuration

Owned by Rishabh Rath
Last updated: Aug 27, 2024
4 min read
Data Flow for Household Data

Step 1: Create XML Data in ADF

  1. Create a Data Flow:

    • In Azure Data Factory Studio, go to the Author tab.

    • Create a new Data Flow and name it HouseholdCaseXML.

  2. Add Source:

    • Select your Azure SQL Database dataset containing household data (hh_sqldata).

    • Ensure columns include household_id, access_to_healthcare_services, availability_of_health_insurance, access_to_clean_drinking_water, and case_id.

  3. Add Derived Column Transformation:

    • Name the transformation hhxml.

    • Create a derived column hh_xml using the following expression. This can be easily copied from the Raw XML section of any form submission within CommCare for a household registration.

    • Replace sections where inputs from the SQL DB are required. For example, case id, household_id and others as seen in this example.

    • Ensure that the instance_id has a UUID value for creating a unique ID for the submission.

    concat( '<?xml version="1.0" ?>', '<data xmlns:jrm="<http://dev.commcarehq.org/jr/xforms>" xmlns="<http://openrosa.org/formdesigner/4E761EC2-20EA-47EB-904E-557041DCAD1C>" uiVersion="1" version="77" name="Household Registration">', ' <household_id>', household_id, '</household_id>', ' <access_to_healthcare_services>', access_to_healthcare_services, '</access_to_healthcare_services>', ' <availability_of_health_insurance>', availability_of_health_insurance, '</availability_of_health_insurance>', ' <access_to_clean_drinking_water>', access_to_clean_drinking_water, '</access_to_clean_drinking_water>', ' <n0:case xmlns:n0="<http://commcarehq.org/case/transaction/v2>" case_id="', lower(case_id), '" date_modified="', currentTimestamp(), '+05:30" user_id="170794ebd70648668c3d8fa7728abfce">', ' <n0:create>', ' <n0:case_name>', household_id, '</n0:case_name>', ' <n0:owner_id>170794ebd70648668c3d8fa7728abfce</n0:owner_id>', ' <n0:case_type>household</n0:case_type>', ' </n0:create>', ' <n0:update>', ' <n0:access_to_clean_drinking_water>', access_to_clean_drinking_water, '</n0:access_to_clean_drinking_water>', ' <n0:access_to_healthcare_services>', access_to_healthcare_services, '</n0:access_to_healthcare_services>', ' <n0:availability_of_health_insurance>', availability_of_health_insurance, '</n0:availability_of_health_insurance>', ' <n0:household_id>', household_id, '</n0:household_id>', ' </n0:update>', ' </n0:case>', ' <n1:meta xmlns:n1="<http://openrosa.org/jr/xforms>">', ' <n1:deviceID>Formplayer</n1:deviceID>', ' <n1:timeStart>', currentTimestamp(), '+05:30</n1:timeStart>', ' <n1:timeEnd>', currentTimestamp(), '+05:30</n1:timeEnd>', ' <n1:username>migration</n1:username>', ' <n1:userID>170794ebd70648668c3d8fa7728abfce</n1:userID>', ' <n1:instanceID>', uuid(), '</n1:instanceID>', ' <n2:appVersion xmlns:n2="<http://commcarehq.org/xforms>">Formplayer Version: 2.53</n2:appVersion>', ' <n1:drift>0</n1:drift>', ' </n1:meta>', '</data>' )

  4. Add Sink:

    • Add a sink transformation to save the generated XML (hhxml) to your Azure SQL Database in the hh_submission table under the final_xml column.

  5. Publish the Data Flow:

    • Save and publish your Data Flow to make it available for execution.

  6. Execute the Data Flow

  • Trigger the Data Flow in your pipeline to generate and store the household case XML in the Azure SQL Database.

Step 2: Submit XML Data to CommCare

  1. Create a New Pipeline:

    • Name it Household Submission.

  2. Add Lookup Activity:

    • Add a Lookup activity to retrieve the final_xml data from the hh_submission table.

    • Configure the SQL query as follows:

    SELECT final_xml FROM hh_submission;

  3. Add Get Credentials Activity:

    • Use a Web Activity to retrieve the API key from Azure Key Vault.

    • Set the method to GET and point to the Key Vault secret.

  4. Add ForEach Activity:

    • Add a ForEach activity to iterate over the results of the Lookup activity.

    • In the ForEach activity, add a Web Activity to submit each final_xml to CommCare’s API.

  5. Configure Web Activity for Submission:

    • Set the HTTP method to POST.

    • Add a header for Authorization using the output from the GetCredentials activity.

    • Set the body to @item().final_xml to send the XML data.

  6. Publish and Execute the Pipeline:

    • Publish the pipeline and run it to submit your household case XMLs to CommCare.