I have a job that consumes incoming files, extracts some information from them and stores it in a database table. I wanted to add a date column to keep track of how many files came in and got consumed in a given day so added a date generation transform to the data flow. Idea was to insert a date to each record on the day it was extracted from the file. Say two files got consumed on 11-08-2018 and two records were extracted, I want to have 11-08-2018 inserted in the date column. Next day, 5 files got consumed, I want 11-09-2018 to be added in the date column.
The date generation transform I have added, however, isn’t adding the correct date to the records. Files coming in on a given day still have the start date. In date generation transform, I have the start and end date as the same.
What would be the setting to implement this correctly? Or is there a better way of achieving this?
Thanks.
Hi,
please use a new column say Extract_Date and map it to sysdate().
it will assign the date on which your job is running.
Regards,
Jayant
Jay_mKumar 
(BOB member since 2016-03-24)
I forgot to report that this is exactly what I did. Created a global variable and used sysdate() to pass current date in the ‘date start’ and ‘date end’ of date generation transform.
I think I dont have to use date generation transform. I can just create a new column and map it to sysdate() as you suggested?
Which one is more efficient, using date_generation transform or mapping a column to sysdate() directly
Hi,
Its better to Use sysdate(), as you mentioned its a recurring load daily, so the date will be adjusted accordingly using sysdate() during the load.
Regards,
Jayant
Jay_mKumar (BOB member since 2016-03-24)