Skip to main content
confused 2 questions and answered incorrectly on both...need more coffee
Source Link
Gortonington
  • 30.9k
  • 3
  • 27
  • 64

I would create one script to duplicate eachyour data extension, you can use WSProxy to do this (Retrieve DE info call, Retrieve Fields call then Create DE call). During this creation, I would make sure to save the names of the DEs created inside of another DE for reference in the second script.

Your second script would and then create and perform query activities via SSJS to fill in these data extensions. You will likely need to create a For loop based off the DE holding your newly created DE names to ensure you create and execute accordinglydata extension.

Simple example of the create then execute script for Query:

<script runat="server">
Platform.Load("Core","1.1") ;

var deName = {{yourTargetDEName}}

var queryName = {{yourQueryName}}

var queryDef = { Name : queryName,

CustomerKey : queryName,
TargetUpdateType : "Overwrite",
TargetType : "DE",

Target:{
Name: deName,
CustomerKey : deName
},
QueryText : "SELECT {{myField}} FROM {{mySourceDE}}"
};

var status = QueryDefinition.Add(queryDef);

sleep(3000); //Wait for 3 seconds
//This helps ensure creation is complete before attempting to execute

var qd = QueryDefinition.Init("myQueryDef");
var status = qd.Perform();


function sleep(milliseconds) {
  var start = new Date().getTime();
  for (var i = 0; i < 1e7; i++) {
    if ((new Date().getTime() - start) > milliseconds){
      break;
    }
  }
}
</script>

Now there are things to consider while doing this in that you will have 15 Queries targeting the same source DE, so you may run into issues there - but with some adjustments and customization, you can get this going to automate your process.

I would create one script to duplicate each data extension, you can use WSProxy to do this (Retrieve DE info call, Retrieve Fields call then Create DE call). During this creation, I would make sure to save the names of the DEs created inside of another DE for reference in the second script.

Your second script would then create and perform query activities to fill in these data extensions. You will likely need to create a For loop based off the DE holding your newly created DE names to ensure you create and execute accordingly.

Simple example of the create then execute script for Query:

<script runat="server">
Platform.Load("Core","1.1") ;

var deName = {{yourTargetDEName}}

var queryName = {{yourQueryName}}

var queryDef = { Name : queryName,

CustomerKey : queryName,
TargetUpdateType : "Overwrite",
TargetType : "DE",

Target:{
Name: deName,
CustomerKey : deName
},
QueryText : "SELECT {{myField}} FROM {{mySourceDE}}"
};

var status = QueryDefinition.Add(queryDef);

sleep(3000); //Wait for 3 seconds
//This helps ensure creation is complete before attempting to execute

var qd = QueryDefinition.Init("myQueryDef");
var status = qd.Perform();


function sleep(milliseconds) {
  var start = new Date().getTime();
  for (var i = 0; i < 1e7; i++) {
    if ((new Date().getTime() - start) > milliseconds){
      break;
    }
  }
}
</script>

Now there are things to consider while doing this in that you will have 15 Queries targeting the same source DE, so you may run into issues there - but with some adjustments and customization, you can get this going to automate your process.

I would create one script to duplicate your data extension, you can use WSProxy to do this (Retrieve DE info call, Retrieve Fields call then Create DE call) and then create and perform query activities via SSJS to fill in the newly created data extension.

Simple example of the create then execute script for Query:

<script runat="server">
Platform.Load("Core","1.1") ;

var deName = {{yourTargetDEName}}

var queryName = {{yourQueryName}}

var queryDef = { Name : queryName,

CustomerKey : queryName,
TargetUpdateType : "Overwrite",
TargetType : "DE",

Target:{
Name: deName,
CustomerKey : deName
},
QueryText : "SELECT {{myField}} FROM {{mySourceDE}}"
};

var status = QueryDefinition.Add(queryDef);

sleep(3000); //Wait for 3 seconds
//This helps ensure creation is complete before attempting to execute

var qd = QueryDefinition.Init("myQueryDef");
var status = qd.Perform();


function sleep(milliseconds) {
  var start = new Date().getTime();
  for (var i = 0; i < 1e7; i++) {
    if ((new Date().getTime() - start) > milliseconds){
      break;
    }
  }
}
</script>
Source Link
Gortonington
  • 30.9k
  • 3
  • 27
  • 64

I would create one script to duplicate each data extension, you can use WSProxy to do this (Retrieve DE info call, Retrieve Fields call then Create DE call). During this creation, I would make sure to save the names of the DEs created inside of another DE for reference in the second script.

Your second script would then create and perform query activities to fill in these data extensions. You will likely need to create a For loop based off the DE holding your newly created DE names to ensure you create and execute accordingly.

Simple example of the create then execute script for Query:

<script runat="server">
Platform.Load("Core","1.1") ;

var deName = {{yourTargetDEName}}

var queryName = {{yourQueryName}}

var queryDef = { Name : queryName,

CustomerKey : queryName,
TargetUpdateType : "Overwrite",
TargetType : "DE",

Target:{
Name: deName,
CustomerKey : deName
},
QueryText : "SELECT {{myField}} FROM {{mySourceDE}}"
};

var status = QueryDefinition.Add(queryDef);

sleep(3000); //Wait for 3 seconds
//This helps ensure creation is complete before attempting to execute

var qd = QueryDefinition.Init("myQueryDef");
var status = qd.Perform();


function sleep(milliseconds) {
  var start = new Date().getTime();
  for (var i = 0; i < 1e7; i++) {
    if ((new Date().getTime() - start) > milliseconds){
      break;
    }
  }
}
</script>

Now there are things to consider while doing this in that you will have 15 Queries targeting the same source DE, so you may run into issues there - but with some adjustments and customization, you can get this going to automate your process.