Conditional Logic in Data Integration Studio

[DavidPhillips2]

Is there a way to conditionally run a transformation in Data Integration Studio? I am working on automating overnight edit checks. However, I do not want to spam recipients if there are no edit records in a table. I am using the Publish to Email transformation to send data in a table. I see some objects in Data Integration Studio that look like there for conditional logic but I do not know how they work. I am using Data Integration Studio Version=4.902.

[Patrick · Accepted solution]

---

@DavidPhillips2 wrote:

Is there a way to conditionally run a transformation in Data Integration Studio? I am working on automating overnight edit checks. However, I do not want to spam recipients if there are no edit records in a table. I am using the Publish to Email transformation to send data in a table. I see some objects in Data Integration Studio that look like there for conditional logic but I do not know how they work. I am using Data Integration Studio Version=4.902.

---

I'm really pleased you're asking this question. Makes the effort for answering a question here less of a waste. 

The conditional start/end transformation will give you what you're after. They simply wrap a macro around all the transformations in-between them. The condition you then define in the Conditional Start bit under the condition tab get's used for either executing the macro wrapped code or not. 

In below picture of the attached sample code the condition used in node 4 is: &createCSV=1

Macro variable &createCSV gets created in node 3 with a value of either 0 or 1. Nodes 5 & 6 get only executed if the condition is True (if the condition is False then there is a %goto in the generated macro which simply jumps to the end of the macro).

The template code is fully working and self contained (only using WORK for storing files).

Should you want to import and execute the job then the only change you need to apply is in node 8 using a valid To email address. 

[ErikLund_Jensen]

Hi @Patrick 

This is not a question or suggestion, just a comment. I had an interesting problem not long ago. A job failed in batch and gave syntax errors, but everything looked fine and worked when the job was opened and submitted in DI Studio.

The error was "CARDS or DATALINES not allowed within a macro definition". The job had user-written code, where a list of constant texts was implemented as datalines in a step, but not within a macro definition. So how did a macro get into the picture?

It turned out that  somebody had decided that the job should run based on conditions, so he made another job with almost the same name, included the whole original job as a "transformation" with a conditional start-end around it, replaced the original job in the LSF flow and tested his conditional logic only, because he knew the original job worked. 

The list was converted to a permanent table and the conditional start-end built into the original job instead, but because of the similar names I didn't realize that is was actually another job that failed, so it took a while to figure out what was going on in a data warehouse with several thousand jobs made by a dozen developers in different departments.

[Patrick]

@ErikLund_Jensen 

A good reminder to avoid datalines/card within production worthy DIS jobs unless there is a very good reason for it.

For me the only place where I might consider this is in adhoc jobs for maintenance of control tables - and these jobs are then for a BAU team only, don't get scheduled but executed directly out of DIS if any change is required. ...and I'm only using this approach on sites where I don't trust a BAU team that they will be "able" to manually maintain a config text file which a BAU adhoc job could load into a control table.