Dynamically Deleting External Files in DI Studio

[Soulbroda]

I have a task that requires an external file used in a load job to be deleted everyday and don't know how to go about it.

The external file is auto generated everyday through a PowerShell script. 

[ChrisLysholm · Accepted solution]

Hello,

I believe the solution would involve user-written code:Working with User-Written Code

There are a number of ways that the user-written code could be introduced into the process flow.

If deletion of external file can be executed as a first step in the process flow, then adding precode at the job level may suffice.

If the physical filename is static and and know prior to job execution, then the user-written code below may work for your use case.

The only parameter in this code is <physical-filename> and this value would need to match the 'File name:' option on the 'File Location'  tab of the External file circled in your image below.

Note that this code does not include error notification or handling. Additionally, no output table is written, so return codes would not be captured in a table for further processing.

filename fn1 "<physical-filename>";

data _null_;  
if fexist("fn1") then
   rc=fdelete("fn1");
run;

[Soulbroda]

Thanks very much for your response. I saw a similar solution shared online which involved using conditional start and conditional end transformation (but used for archiving instead). As I said, I am fairly new to using SAS and will be glad if you could break it down more.

Kindly see the below image if that's how the job will look.

[ChrisLysholm]

Well the conditional start/end transformations would be used to execute processes conditionally. My understanding from your original problem statement was that an external file required deletion at some point in the process flow. If there were conditions when the external file should not be deleted, then yes, I could see a use for conditional start/end in your process flow.

But I believe from your original problem statement that an external file needed to be deleted, perhaps after loading?

So in this case, a simple solution would be to define a job postcode that deletes the external file upon completion of a load. 

If the load is not successful, then perhaps that is a condition to not delete the external file, and in this case, conditional start/end would be helpful.

[Soulbroda]

Works like a charm. Thanks alot!

[Patrick]

@Soulbroda 

As an extension the solution you've already accepted:

Even when using user written code you still should aim for keeping things as metadata driven as possible. In your case I'd go for code which uses the DIS generated macro variable &_INPUT for deletion of the external file. This allows to implement code which will still work even when pointing the external file metadata to a different location (i.e. as part of migration to another environment).

I personally prefer using separate nodes for such tasks over adding it as pre- or post-code to existing nodes. This to have as much of what's happening directly visible in the job canvas.

How this could look like:

Code in transformation:

......Generated code by transformation
%let _INPUT_count = 1;
%let _INPUT = <path>/calendar.csv;
%let _INPUT_filetype = ExternalTable;

...... manually user written code
data _null_;
    fname="tempfile";
    rc=filename(fname, "&_INPUT");
    if rc = 0 and fexist(fname) then
       rc=fdelete(fname);
    rc=filename(fname);
run;

The user written code I've used is an almost straight copy from the documentation.

https://go.documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p0h945u5r0cv6yn1u6qs35hiqt9t.... 

If deletion of external files is something that you need to implement for multiple files and eventually in different DIS jobs then consider creating a reusable object via a DIS custom transformation.

In doing so you could also implement something which allows to connect multiple files to the transformation. The generated macro variable &_input_count (or &_output_count if you want to have the files on the target side) then allows you to easily implement a macro loop and delete all the connected file or table objects.

[Soulbroda]

Thanks for your response Patrick. I would like a bit of clarity on this though;

"I personally prefer using separate nodes for such tasks over adding it as pre- or post-code to existing nodes. This to have as much of what's happening directly visible in the job canvas."

Does this come after the load table? It looks like it's a standalone as I can't see any connecting lines to it.

[Patrick]

---

@Soulbroda wrote:

Thanks for your response Patrick. I would like a bit of clarity on this though;

"I personally prefer using separate nodes for such tasks over adding it as pre- or post-code to existing nodes. This to have as much of what's happening directly visible in the job canvas."

Does this come after the load table? It looks like it's a standalone as I can't see any connecting lines to it.

---

You place such a node anywhere you like but of course you probably want to have the source successfully loaded into target (a permanent table) before you delete it. 

You can either connect the user written code node (or the custom transformation) to the already existing external file metadata object in the canvas and then just make sure this node get called at the right time (rather at the end of the job flow) or you can also add another copy of the external table object to your job - the outcome will be the same, that's just about "visualisation".

[AngusLooney]

No, it should not be a User Written Node, it should be a Custom Transform, coded to make it a generic, reusable addition to the over all "Toolbox".

There is rarely a case in my experience where a User Written Noe isn't an instance of generic task that should be a User Transform!

[Soulbroda]

A follow-up question to this.

While migrating the code to the test environment, I forgot to change the path to delete files from to the Test environment path instead of the Development environment path. I wanted to check out the code for a quick fix but the SAS Admin gave me a line of code to use instead.

libname test base "!MYORGEIMInbox_BasePath";
proc datasets library=test;run;quit;

This looks abit confusing and I tried it this way:

libname test base "\\myorg.local\shared\BiDATA\SAS DDS\Development\Inbox\UnstructuredFiles\";
proc datasets library=test;run;quit;

filename fn1 "\\myorg.local\shared\BiDATA\SAS DDS\Development\Inbox\UnstructuredFiles\eftusfile.txt";
data _null_;
if fexist("fn1") then
rc=fdelete("fn1");
run;

filename fn2 "\\myorg.local\shared\BiDATA\SAS DDS\Development\Inbox\UnstructuredFiles\eftcdfile.txt";

data _null_;
if fexist("fn2") then
rc=fdelete("fn2");
run;

However, I get an error every time I run this. Please, what am I getting wrong