SCD Type 2 Loader Modification

[snakes]

Hello All,

We would like to make some changes to the SCD Type 2 loader in SAS DI. Is it possible to copy the standard loader to the custom transformation folder so that we can do changes to it?

The reason we want to do it is to enhance the option "Close out records not in source table". Currently it set sets the 
ETLS_CLSDATE = DATE();

And we would like to specify the date and not the current date when the program is running.

Regards,

Nagraj

[LinusH]

That is one flaw many have discovered.

Unfortunately, you cannot tweak the DI Studio native transformations, unless you a re a skilled Java developer.

What you can do is take the generated code  from the standard transformation, and paste into a User Written Transformation.

It will not be as "nice" as the original one, and some adjustments/configuration to make it work.

[snakes]

I am used to programming in java. If you can point me to the .jar or .class file I can try to reverse engineer it. Of course source code will be a lot easier 🙂

[LinusH]

This is a outside my realm. I found this (old) paper:

https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://support.sas.com/resources/...

May contact you SAS representative or open a SAS track to get better/more detailed guidelines.

One caveat, if you build you own java transformation, that will for not be eligible for migration to Viya 4 when that day comes (please verify with SAS).

[snakes]

Thanks, Yes I am aware of this. I was hoping something will have changed after such a long time. :-). I have a basic custom transform that does the job. Back to the drawing board for now.

[LinusH]

Another (and not ideal) work-around if you wish to use the existing transformation:
- specify %let etls_debug = 1; in your job(s), or perhaps in appserver_autoexec_usermods.
- Have program (macro preferably) in your postcode of your SCD2 transform (or as a separate transformation) that picks up work.etls_close, modify the end date (for records equal today - drop the rest), and re-apply it to you target table. Of course, if you are expecting a big amount of records to be closed out each batch, this is probably not acceptable from a performance standpoint.

[snakes]

Thanks that is another method  that I did not think about. Will look into it.