Skip to Content

Hi,

I had a requirement to store Flat Files dynamically along with the current time-stamp attached with filename. When I searched through, didn’t find any specific material for the same. So now, As I’ve implemented it, I’de like to share the process with you 🙂

  1. Create a Flat File Format provide the location in Root directory where you want to store the file. Leave file name empty here.
  2. Now in your job – Create a global variable e.g. $G_File_Name.  Then take a script before the DF and write the below code in Script – 
    ‘$G_File_Name = YourFileName_’||TO_CHAR(sysdate(),’MMDDYYYY’)||’_’||TO_CHAR(sysdate(),’HHMISS’)||’.’||’txt’;’
    Note: You can change the file extension as per
    requirement. e.g. ‘.csv’ or ‘.txt’
  3. Assign this global variable ‘$G_File_Name’ as file name to flatfile format in your dataflow where you are using the flat file as Target.
  4. So every time you run the job it will create a file with Filename along with the current timestamp attached to it.
    e.g. In our case it will generate a file as ’YourFileName_11302015_021745.txt’.

Thanks,

Gokul

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Venkataraja k

    Thanks for the logic, I would get a report for the previous month, what could be the logic for this?

    the below one I have created, but when I run on 1st of every month it pulls for a prevoius month. If I would like to run on any day in that month, it should return previous month, what’s the logic?

    $G_Report_Name=TO_CHAR(sysdate()-1,’YYYYMM’)||’DesignStart_Downloads_ARMonARM’||’.csv;

     

    (0) 
  2. Gokul Gawande Post author

    Hi Venkataraja,

     

    Perhaps you can make use of MONTH function of BODS and write the logic accordingly. Below given is some extra information about the function:

    MONTH: Returns the month number of the input date.

    • Syntax: month( [in] InputDate As datetime ) As int
    • Example: month (‘09.23’)
    • Result: 9

    In your case you can do month (sysdate) – 1. And it should return exactly the previous month irrelevant of when yo are executing the script. Below is pseudo code for your reference. Change/modify as per your requirement and check for syntax:

    $G_Month= month (sysdate) -1;

    $G_Report_Name=$G_Month||’DesignStart_Downloads_ARMonARM’||’.csv;

     

    (0) 

Leave a Reply