Connect External File tot Custom Transformation

[ThierryHerrie]

Hi,

I would like tot connect an External File object to a custom Tranformation in DI Studio.

However it doesn't work, I can only connect to a File Reader or User Written Code object.

Is is possible to connect it to a own written Tranformation?

I'm using: SAS 9.4 M4, DIS 4.902

[Patrick]

@ThierryHerrie 

That appears not to be possible. But given that DIS doesn't clean-up macro variables generated within a transformation and assuming you just need these generated macro variables, you could "trick" DIS using the below construct:

What are you doing logically within this custom transformation? May be there are other ways to implement what you need.

[ThierryHerrie]

Thanks for your message Patrick. It does however clean-up the _input macrovariable.

With the custom tranformation I want to check the external file for any changed columns. For instance when an external file has an extra column, I don't want to load the external file.

[LinusH]

If you use a File Reader transformation, any added fields (to the right) wouldn't be imported, so why do you need this check?

If the file has any other change that violates the import code, the job would fail, which to me would be a correct behavior.

My personal opinion that this type of data governance "programming" is shifting focus to what is really important. Usually file extract from source are very stable and doesn't need this type of auditing. I would more focus on the data contents instead.

That said, you could have a separate File Reader, reading the source file into a single SAS variable, which then can parse separately, and do your logic there. But again, IMO a bit over complicated.

[ThierryHerrie]

Yes, fields to the right are no problem for the import process. It is however a problem when the fields are switched. So the numbers of column "Revenue" are stored at the location where "Number of employees" are stored. Both are numeric so the import proces is fine, technically.

I agree with you that this is a technical solution for something that should be part of good data governance. I will put that on the agenda also, thanks for reminding me this.

Thanks all for your help. For now I think I will skip this step or copy / past code in a User Written transformation. Or create an autocall macro, something like that.

[Patrick]

@ThierryHerrie 

If this is just for a single external source then I'd definitely go for a user written transformation if a technical solution at all.

But just for the sake of it below will work IF you map source to target in the user written transformation AND in the custom transformation "Generate column mapping macros" is NOT ticked.

In the User Writen node add the following code: %let prev_INPUT_count=&_INPUT_count;

You then can use the created macro variable to process the mapping info from the user written code for 1 or multiple source files.

With not generating the mapping macros below what the custom transformation generates for me:

/*==========================================================================* 
 * Step:            cust_trans                            A7BNXS3V.BW00060M * 
 * Transform:       cust_trans                                              * 
 * Description:                                                             * 
 *==========================================================================*/ 

%let transformID = %quote(A7BNXS3V.BW00060M);
%let trans_rc = 0;
%let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 

%let _INPUT_count = 0; 
%let _OUTPUT_count = 0; 



%rcSet(&syserr); 
%rcSet(&sysrc); 
%rcSet(&sqlrc); 

[Patrick]

@ThierryHerrie 

And as another way of doing it.

Even though the File Reader doesn't generate mapping macros should you by any chance not use a hard coded file name but a macro variable to point to the physical file then you would have all the information you need to add some post code into the file reader.

Define the file reader target as view so the generated code doesn't actually read the data but only defines the view.

You then can use post code (could also be the call to an autocall macro of course). A Proc Contents will give you the column definitions as per external file and the macro variable used in the external file gives you the path to the physical instance of the file.  

/*==========================================================================* 
 * Step:            File Reader                           A7BNXS3V.BW00060N * 
 * Transform:       File Reader                                             * 
 * Description:                                                             * 
 *                                                                          * 
 * Source Table:    ext_file - &ext_file                  A7BNXS3V.C50000Y8 * 
 * Target Table:    File Reader - work.W2UTR0VL           A7BNXS3V.BZ0002KB * 
 *==========================================================================*/ 

%let transformID = %quote(A7BNXS3V.BW00060N);
%let trans_rc = 0;
%let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 

proc datasets lib = work nolist nowarn memtype = (data view);
   delete W2UTR0VL;
quit;

data work.W2UTR0VL / view = work.W2UTR0VL ; 
   infile "&ext_file"
          lrecl = 1000
          delimiter = ','
          dsd
          missover
          firstobs = 2; 
   ; 
   attrib listing_id length = 8
      format = best32.
      informat = best32.; 
   attrib date length = 8
      format = ddmmyy10.
      informat = yymmdd10.; 
   attrib available length = $1
      format = $1.
      informat = $1.; 
   attrib price length = 8
      format = dollar8.2
      informat = dollar8.2; 
   
   input listing_id date available price; 
   
run; 

%rcSet(&syserr); 

/*---- Start of Post-Process Code  ----*/ 

proc contents data=_last_ out=contents;
run;
%put &ext_file;
/*---- End of Post-Process Code  ----*/ 

[AngusLooney]

In general, at least in my experience, you're going to be populating your External File Object's physical file path/names with run-time values, from a directory read or a control table, and very often be processing multiple files at a time.

So the Ext File has &filepath. as it's physical path value (remember to check the "double quotes" option).

My preference approach is to put any file processing steps in an parameterised "inner job" and feed it 1 to N real filenames via a Loop construct, passing the appropriate value(s).

This has the advantage of allowing you to use the value of the filename as a field value in your output, for instance to support audit and/or debuging uses.

When developing the inner job, just set a default value pointing to a test file, so you can test the job in isolation.

You may find that these inner jobs morph into "toolbox" items - for instance, a generic "delete a file" job.

Empty target dataset, read list of files to process, loop throgh them one by one appending to target as you do.

[ThierryHerrie]

Thanks all for replying to my question. Great community!!

I think, for now, I will call a macro in a User Written transformation and try to do this only with the most risk full external files.

Cheers!

Thierry