[SalesForce] Bulk insert/update Data extension using SSJS marketing cloud

I have a REST request which gives me a large volume of data in the response. I am able to Platform.Function.ParseJSON which gives me an Array list.

The array size is close to 40 odd rows. Each row has 4+ fields. I need to insert this data to a DE.

I can use Platform.Function.InsertData but I think I need to loop through the 40 rows and call the Insert function each time.

Is there any way either SSJS or AMPScript support Bulk insert data into a Data Extension.

Greatly appreciate any feedback/guidance.

Thanks,
Raj

Best Answer

There are a few options for you to do this. There is:

  1. A SOAP API call
  2. A WSProxy call
  3. A REST API call
  4. SSJS Core function

For SOAP API

(docs) You would build your envelope containing multiple 'Objects' nodes - one for each row you want to add in. You can also shift this to be Upsert (updateAdd SaveAction) to make sure it does not throw an exception if the row already exists.

Example:

    <UpdateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <Options>
            <SaveOptions>
               <SaveOption>
                  <PropertyName>DataExtensionObject</PropertyName>
                  <SaveAction>UpdateAdd</SaveAction>
               </SaveOption>
            </SaveOptions>
         </Options>
            <Objects xsi:type="DataExtensionObject">
                <CustomerKey>myDE</CustomerKey>
                <Keys>
                    <Key>
                        <Name>Subscriberkey</Name>
                        <Value>sample@example.com</Value>
                    </Key>
                </Keys>
                <Properties>
                    <Property>
                        <Name>Active</Name>
                        <Value>Y</Value>
                    </Property>
                </Properties>
            </Objects>
                        <Objects xsi:type="DataExtensionObject">
                <CustomerKey>myDE</CustomerKey>
                <Keys>
                    <Key>
                        <Name>Subscriberkey</Name>
                        <Value>sample1@example.com</Value>
                    </Key>
                </Keys>
                <Properties>
                    <Property>
                        <Name>Active</Name>
                        <Value>Y</Value>
                    </Property>
                </Properties>
            </Objects>
    </UpdateRequest>

For WSProxy

(docs) This is basically a simplified way to do the above inside of SFMC SSJS. Same basic idea, just using JSON and other more native JS capabilities to do so.

Example:

    var api = new Script.Util.WSProxy();

    /* Build DE Object */
    var updateArr = [
      {
        CustomerKey: 'DE_Example',
        Properties: [
             {
                Name: 'FirstName',
                Value: 'Tester'
            },
             {
                Name: 'LastName',
                Value: 'Testerson'
            },
             {
                Name: 'ModifiedDate',
                Value: Platform.Function.Now()
            },
             
        ]
    },
      {
        CustomerKey: 'DE_Example',
        Properties: [
             {
                Name: 'FirstName',
                Value: 'Tester2'
            },
             {
                Name: 'LastName',
                Value: 'Testerson2'
            },
             {
                Name: 'ModifiedDate',
                Value: Platform.Function.Now()
            },
             
        ]
    }
  ];

    var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

    var res = api.updateItem('DataExtensionObject', updateArr, options);

REST API

This one I offer up as it sounds like you are already pulling in REST API functions so may be something to shift in without much issue:

POST /data/v1/async/dataextensions/{{myDEKey}}:key/rows
Host: {{mySubDomain}}.rest.marketingcloudapis.com
Content-Type: application/json
Authorization: Bearer {{authToken}}

{
   "items": [{
      "FirstName":"Bobby",
      "LastName" : "Jones",
      "ZipCode": "23456"
   },
   {
      "FirstName":"Sam",
      "LastName" : "Sneed",
      "ZipCode": "23456"
   }]
}

Rows.Add

(docs) This one is add only though, so if there is an existing row, it could cause an issue - so will need to be considered.

Example:

var arrContacts =  [
      {Email:"jdoe@example.com",FirstName:"John",LastName:"Doe"},
      {Email:"aruiz@example.com",FirstName:"Angel",LastName:"Ruiz"}
     ];

var birthdayDE = DataExtension.Init("birthdayDE");
birthdayDE.Rows.Add(arrContacts);
Related Topic