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 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 at 18:43
1

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>

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.