2

I have a functioning code through which I update the query text through SSJS. This is working as expected. I am trying to expand on this and want to update the target Data extension as well. Is this possible? I have a template query and using that would like to use it for multiple languages and then create separate data extensions with that data through execution of that query.

I was trying to achieve this through the below code.

var update_status = qd.Update({
  Name: queryName,
  QueryText: query,
  TargetUpdateType: "Overwrite"
  TargetType: "DE",
  Target: {
    Name: languageDEName,
    CustomerKey: languageDEKey
  },
});
var perform_status = qd.Perform();

Would really appreciate any pointers or guidance.

  • Sorry, it's not getting 100% clear: Do you want to create a data extension or update a data extension? In your code, you are using WSProxy, which basically emulates the SOAP API. So everything that works with SOAP API will be possible also with WSProxy. this includes the methods update and create on the data extension object. To be fair, there are scarce examples for WSProxy documented, so it can be confusing at first. For "create" via SOAP you can find the docs here: developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/… – Jonas Lamberty Jun 28 '19 at 7:11
  • Thanks. I am trying to update a query, a query uses a data extension to populate the data set. I want to change that resulting data extension based on a specific parameter set. Ex: when the language is english, then the query should populate a DE named Data_En, when it's chinese, the query should populate a DE named Data_Cn. I am not trying to create a DE. Hope this clarifies my question. – Monomit Bhowmik Jun 29 '19 at 18:43
2

Using WsProxy, you need to supply the ObjectID for the specific QueryDefinition object you are updating. The ObjectID is not the same as External Key or CustomerKey. It is available by copying the URL link to the Query Activity in the Activities tab within Automation studio interface. It's the GUID at the end of the URL. I'm not sure how else to retrieve this, it may not be available programmaticaly.

To update the target you only need to pass the DE name.

Updating via this method does not validate your query. If your target query schema is incompatible with the query, it will fail when you run it.

how to copy the url for a query activity in order to get the objectID

<script runat="server">
Platform.Load("Core", "1.1.5");
var prox = new Script.Util.WSProxy();
var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};
var data = {
  CustomerKey: "1111111-2222-3333-4444-5555555555",
  ObjectID: "aaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeee",
  DataExtensionTarget: {
    Name: 'New DE target name'
  }
};
var desc = prox.updateItem("QueryDefinition", data, options);

Write(Stringify(desc));
</script>
| improve this answer | |
  • You can make a request with the CustomerKey and get the ObjectID from the response. Also, I have done an automation with one with one Script Activity and a Query Activity (the Script Activity modifying the target DE of the Query), when I use this request with just the DE Name in "DataExtensionTarget", the query result went to the Data Extension it was before. Sending the DEname and DE ObjectID solved this problem – Leto Jun 24 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.