Save Flat-Files along with current date and Timestamp(FileName+Timestamp) using Global Variable
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 🙂
- Create a Flat File Format provide the location in Root directory where you want to store the file. Leave file name empty here.
- 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’
- 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.
- 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 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?
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.
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;