[Azure Data Factory] Parent Cases Flow Configuration

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.

   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.