topic Re: SCD Type 2 Loader for Update/overwritten data (Type 1 Columns) in SAS Programming

SCD Type 2 Loader for Update/overwritten data (Type 1 Columns)

vironica — Sat, 17 Jul 2010 07:49:53 GMT

Dear All,

This is regarding transformation object : SCD Type 2 Loader for Update/overwritten data (Type 1 Columns) in SAS Data Integration Studio 4.2.

Question:
How to get the date/time(sysdate) when any records got Updated/overwritten by (Type 1 Columns) so that i can keep the time information into my update_dt field in my target table.

Please advise.

Thank you.

Regards,
V

Re: SCD Type 2 Loader for Update/overwritten data (Type 1 Columns)

Patrick — Sat, 17 Jul 2010 10:42:46 GMT

Hi V

Have you tried to add your target field update_dt to "Options/Additional Loader Options/Load time column"?

HTH
Patrick

Re: SCD Type 2 Loader for Update/overwritten data (Type 1 Columns)

vironica — Mon, 19 Jul 2010 01:55:37 GMT

Dear Patrick,

Yes, great ... it works well for the updated records.

If i also need to get the sysdate for new records inserted, how to get this information ?

Please advise.

Thank you very much.

Best Regards,
V Message was edited by: vironica

Re: SCD Type 2 Loader for Update/overwritten data (Type 1 Columns)

Patrick — Mon, 19 Jul 2010 12:54:59 GMT

Hi V

Once you've defined your variable as "Load time column" the SCD2 loader will populate it for all cases (update, insert - SCD1, SCD2 and mixed loading).

The value used to populate the "Load time column" is actually not &sysdate but a string based on &sysdate and &systime and stored in a macrovar with name &etls_loadtime.

If you look into the generated code of the SCD2 loader you will find something like this: update_dt = input("&etls_loadtime", datetime20.);

If your SAS coding level is good enough then it's worth taking the time and try to understand the generated code. It helps a lot to understand what's going on behind the scene if you have to debug a problem - and as some code parts are rather advanced there is also a lot to learn.

HTH
Patrick