This article explains how to convert a regular Data Extension into a TriggeredSend Data Extension using server-side Javascript.
When Marketing Cloud fails to provide you with a feature, just make one yourself
– Every MC developer
Why use it
Marketing Cloud does not allow us to convert a regular Data Extension to a TriggeredSend Data Extension through its interface.
Usually, this is not a problem. We can simply delete the existing Data Extension and recreate it from the Contact Builder.
But what happens when there are already several hundreds of records in the Data Extension and the number of fields is 20 or more?
In this case, the following method is perfect for you!
How it works
One does not simply walk into Mordor. It would have been too simple to just change a property of a Data Extension and be done with it.
Instead we are going to break the process into several steps:
- Duplicate the original Data Extension.
- Copy the records of the original Data Extension and its Customer Key.
- Add the TriggeredSend required fields, properties and Template External Key to the duplicated Data Extension.
- Insert the records in the duplicated Data Extension.
- Delete the original Data Extension.
- Replace the Customer Key by the Customer Key of the original Data Extension.
As a result, the newly created Data Extension will not only have the same fields and records as the original one, but also the properties of a TriggeredSend Data Extension.
Javascript functions
In order to make our code readable and generic, the usage of multiple functions is necessary.
getTSDtemplateKey
This function retrieves the External Key of the TriggeredSend Template.
function getTSDtemplateKey() {
var templateKey = "No template key";
var prox = new Script.Util.WSProxy();
var cols = ["Name","CustomerKey"];
var filter = {
Property: "Name",
SimpleOperator: "equals",
Value: "TriggeredSendDataExtension"
}
var req = prox.retrieve("DataExtensionTemplate", cols, filter);
if(req["Status"] == "OK") {
templateKey = req["Results"][0].CustomerKey;
}
return templateKey;
}retrieveFields
This function showcases the perfect way to retrieve the properties of the fields from a Data Extension.
There are only 2 exceptions: SubscriberKey and EmailAddress, which we know will always be in a TriggeredSend Data Extension.
Remember that some fields require some properties.
For exemple, a Text field should always have a MaxLength but others can do without.
function retrieveFields(deName, isTSD, exclude) {
var fields = [];
var isTSD = isTSD || false;
var exclude = (isTSD == true) ? ["SubscriberKey","EmailAddress"] : exclude;
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var prox = new Script.Util.WSProxy();
var cols = ["Name","MaxLength","FieldType","IsRequired","Ordinal","DefaultValue","IsPrimaryKey"];
var filter = {
Property: "DataExtension.CustomerKey",
SimpleOperator: "equals",
Value: req[0].CustomerKey
}
var req = prox.retrieve("DataExtensionField", cols, filter);
if(req["Status"] == "OK") {
var res = req["Results"];
for(k in res) {
if(!inArray(exclude,res[k].Name)) {
var obj = {
"Name" : res[k].Name,
"FieldType" : res[k].FieldType,
"IsRequired" : res[k].IsRequired,
"IsPrimaryKey" : (isTSD != false) ? false : res[k].IsPrimaryKey,
"Ordinal" : res[k].Ordinal
}
if(res[k].FieldType == "Text") obj.MaxLength = res[k].MaxLength;
if(res[k].DefaultValue != null) obj.DefaultValue = res[k].DefaultValue;
fields = fields.concat(obj);
fields.sort(function (a, b) { return (a.Ordinal > b.Ordinal) ? 1 : -1 } );
}
}
} else {
return "No fields were retrieved from \"" + deName + "\"";
}
return fields;
}retrieveRecords
The function retrieves the records from the original Data Extension and, in case there are no fields named SubscriberKey and EmailAddress, adds some additional data to these records.
In case there was no SubscriberKey field, the script will add this column and try to fill in the email address if available. Otherwise, the SubscriberKey will be equal to the word TEMP concatenated with a timestamp.
In case there was no EmailAddress field, the script will add this column and simply fill in a default email value: unknown@mail.com.
function retrieveRecords(deName, isTSD) {
var isTSD = isTSD || false;
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var DE = DataExtension.Init(req[0].CustomerKey);
var records = DE.Rows.Retrieve();
if(isTSD != false) {
for(var i = 0; i < records.length; i++) {
if(records[i]['SubscriberKey'] == null) {
if(records[i]['EmailAddress'] != null) records[i]['SubscriberKey'] = records[i]['EmailAddress'];
else if(records[i]['Email'] != null) records[i]['SubscriberKey'] = records[i]['Email'];
else records[i]['SubscriberKey'] = "TEMP" + timestamp();
}
if(records[i]['EmailAddress'] == null) {
if(records[i]['Email'] != null) records[i]['EmailAddress'] = records[i]['Email'];
else records[i]['EmailAddress'] = "unknown@mail.com";
}
}
}
return records;
}retrieveFolderId
Retrieves and returns the ID of the folder based on its Name attribute.
function retrieveFolderId(folderName) {
var req = Folder.Retrieve({ Property: 'Name', SimpleOperator: 'equals', Value: folderName });
return req[0].ID || 0;
}deleteDataExtension
Please use this function with care. It permanently deletes a Data Extension when provided with a Data Extension name.
function deleteDataExtension(deName) {
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var objectId = req[0]["ObjectID"];
var prox = new Script.Util.WSProxy();
var res = prox.deleteItem("DataExtension", { "ObjectID": objectId });
return "Data Extension \"" + deName + "\" has been deleted.";
}convertToTSD
In order to convert a regular Data Extension into a TriggeredSend Data Extension we need to fulfill the following requirements:
- TriggeredSend Data Extension should not contain a Primary Key field.
- TriggeredSend Data Extension should be Sendable.
- SubscriberKey and EmailAddress fields should exist and cannot be Nullable.
Using the previously explained functions, the following code creates a Javascript object filled with all the properties and fields necessary for creating a TriggeredSend Data Extension.
Note that in order to update the newly created Data Extension with the original Data Extension’s Customer Key, we need to retrieve the ObjectID property.
function convertToTSD(deName, folderName, isTSD) {
var prox = new Script.Util.WSProxy();
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var CustomerKey = req[0]["CustomerKey"];
var newCustomerKey = Platform.Function.GUID();
var isTSD = isTSD || true;
var fields = retrieveFields(deName, isTSD);
var records = retrieveRecords(deName, isTSD);
var folderId = retrieveFolderId(folderName);
var newName = deName + ' --CLONE-- ' + timestamp();
var clone = {
'CustomerKey' : newCustomerKey,
'Name' : newName,
'CategoryID': folderId,
'Fields' : fields
}
if(isTSD != false) {
var add = {
"IsSendable": true,
"SendableSubscriberField": {
"Name": "_SubscriberKey"
},
"SendableDataExtensionField": {
"Name": "SubscriberKey"
},
"Template": {
"CustomerKey": getTSDtemplateKey()
}
}
for(k in add) {
clone[k] = add[k];
}
}
if(fields.length > 0) {
var res1 = prox.createItem("DataExtension", clone);
var newObjectID = res1["Results"][0].NewObjectID;
}
if(records.length > 0) {
var DE = DataExtension.Init(newCustomerKey);
var res2 = DE.Rows.Add(records);
}
if(newObjectID.length > 0) {
var del = deleteDataExtension(deName);
var up = prox.updateItem("DataExtension", { "ObjectID": newObjectID, "Name": deName, "CustomerKey": CustomerKey });
}
return "The Data Extension \"" + deName + "\" has been successfuly converted to a TriggeredSend Data Extension and moved to the \"" + folderName + "\" folder";
}Full code
Copy/paste this code and see it in action! But please make sure specify an existing Data Extension name and an existing Folder name.
<script runat="server">
Platform.Load("core", "1.1");
var resp = convertToTSD('MyAwesomeDataExtension','MyDestinationFolder');
Write(resp);
function convertToTSD(deName, folderName, isTSD) {
try {
var prox = new Script.Util.WSProxy();
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var CustomerKey = req[0]["CustomerKey"];
var newCustomerKey = Platform.Function.GUID();
var isTSD = isTSD || true;
var fields = retrieveFields(deName, isTSD);
var records = retrieveRecords(deName, isTSD);
var folderId = retrieveFolderId(folderName);
var newName = deName + ' --CLONE-- ' + timestamp();
var clone = {
'CustomerKey' : newCustomerKey,
'Name' : newName,
'CategoryID': folderId,
'Fields' : fields
}
if(isTSD != false) {
var add = {
"IsSendable": true,
"SendableSubscriberField": {
"Name": "_SubscriberKey"
},
"SendableDataExtensionField": {
"Name": "SubscriberKey"
},
"Template": {
"CustomerKey": getTSDtemplateKey()
}
}
for(k in add) {
clone[k] = add[k];
}
}
if(fields.length > 0) {
var res1 = prox.createItem("DataExtension", clone);
var newObjectID = res1["Results"][0].NewObjectID;
}
if(records.length > 0) {
var DE = DataExtension.Init(newCustomerKey);
var res2 = DE.Rows.Add(records);
}
if(newObjectID.length > 0) {
var del = deleteDataExtension(deName);
var up = prox.updateItem("DataExtension", { "ObjectID": newObjectID, "Name": deName, "CustomerKey": CustomerKey });
}
return "The Data Extension \"" + deName + "\" has been successfuly converted to a TriggeredSend Data Extension and moved to the \"" + folderName + "\" folder";
} catch(err) {
Write("Errors from convertToTSD function: " + Stringify(err));
}
}
function deleteDataExtension(deName) {
try {
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var objectId = req[0]["ObjectID"];
var prox = new Script.Util.WSProxy();
var res = prox.deleteItem("DataExtension", { "ObjectID": objectId });
return "Data Extension \"" + deName + "\" has been deleted.";
} catch(err) {
Write("Errors from deleteDataExtension function: " + Stringify(err));
}
}
function retrieveFolderId(folderName) {
var req = Folder.Retrieve({ Property: 'Name', SimpleOperator: 'equals', Value: folderName });
return req[0].ID || 0;
}
function retrieveRecords(deName, isTSD) {
try {
var isTSD = isTSD || false;
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var DE = DataExtension.Init(req[0].CustomerKey);
var records = DE.Rows.Retrieve();
if(isTSD != false) {
for(var i = 0; i < records.length; i++) {
if(records[i]['SubscriberKey'] == null) {
if(records[i]['EmailAddress'] != null) records[i]['SubscriberKey'] = records[i]['EmailAddress'];
else if(records[i]['Email'] != null) records[i]['SubscriberKey'] = records[i]['Email'];
else records[i]['SubscriberKey'] = "TEMP" + timestamp();
}
if(records[i]['EmailAddress'] == null) {
if(records[i]['Email'] != null) records[i]['EmailAddress'] = records[i]['Email'];
else records[i]['EmailAddress'] = "unknown@mail.com";
}
}
}
return records;
} catch(err) {
Write("Errors from retrieveRecords function: " + Stringify(err));
}
}
function retrieveFields(deName, isTSD, exclude) {
try {
var fields = [];
var isTSD = isTSD || false;
var exclude = (isTSD == true) ? ["SubscriberKey","EmailAddress"] : exclude;
var req = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: deName });
var prox = new Script.Util.WSProxy();
var cols = ["Name","MaxLength","FieldType","IsRequired","Ordinal","DefaultValue","IsPrimaryKey"];
var filter = {
Property: "DataExtension.CustomerKey",
SimpleOperator: "equals",
Value: req[0].CustomerKey
}
var req = prox.retrieve("DataExtensionField", cols, filter);
if(req["Status"] == "OK") {
var res = req["Results"];
for(k in res) {
if(!inArray(exclude,res[k].Name)) {
var obj = {
"Name" : res[k].Name,
"FieldType" : res[k].FieldType,
"IsRequired" : res[k].IsRequired,
"IsPrimaryKey" : (isTSD != false) ? false : res[k].IsPrimaryKey,
"Ordinal" : res[k].Ordinal
}
if(res[k].FieldType == "Text") obj.MaxLength = res[k].MaxLength;
if(res[k].DefaultValue != null) obj.DefaultValue = res[k].DefaultValue;
fields = fields.concat(obj);
fields.sort(function (a, b) { return (a.Ordinal > b.Ordinal) ? 1 : -1 } );
}
}
} else {
return "No fields were retrieved from \"" + deName + "\"";
}
return fields;
} catch(err) {
Write("Errors from retrieveFields function: " + Stringify(err));
}
}
function getTSDtemplateKey() {
var templateKey = "No template key";
var prox = new Script.Util.WSProxy();
var cols = ["Name","CustomerKey"];
var filter = {
Property: "Name",
SimpleOperator: "equals",
Value: "TriggeredSendDataExtension"
}
var req = prox.retrieve("DataExtensionTemplate", cols, filter);
if(req["Status"] == "OK") {
templateKey = req["Results"][0].CustomerKey;
}
return templateKey;
}
function inArray(arr, k) {
var out = -1;
for (var i in arr) {
if (arr[i] == k) out = i;
}
return out;
}
function timestamp() {
var now = new Date();
var out = now.getFullYear()
+ addZero(now.getMonth() + 1)
+ addZero(now.getDate())
+ addZero(now.getHours())
+ addZero(now.getMinutes())
+ addZero(now.getSeconds())
+ random(100, 999);
return out;
function addZero(n) {
return n < 10 ? '0' + n : n
}
function random(min, max) {
min = Math.ceil(min);
max = Math.floor(max);
return Math.floor(Math.random() * (max - min)) + min;
}
}
</script>Considerations
Remember, there is more than one way to skin a cat. This method may be overly complicated, but for the time being, it does the trick perfectly.
Please be aware that the DataExtension.Retrieve() method can only retrieve 2500 records per request. This means that only the first 2500 records will be copied from the original Data Extension. This is a Marketing Cloud limitation and I’m currently looking into how to bypass it.
Credits
Special thanks to Vladimir Silak for helping me to figure out how to retrieve the TriggeredSend Template External Key from the current BU.
Have I missed anything?
Please poke me with a sharp comment below or use the contact form.
