got to upload a file every night and overwrite it every year

[sashu12]

hi all,

I need to write a batch file to upload data from a relational source to a file target(actually i should overwrite the file every night). I should do this until the fiscal year changes and then i need to create a new file for the new year

can any one help me with this
thanks
nithin

 

[blom0344]

Can you specify this in more detail. Are you working on a windows platform. What is the RDBMS? Why do you not use Powercenter to extract the data?

With SQL server you can write an OSQL script, schedule it and then write a cmd script to change the name of the file.

Ties Blom

 

[sashu12]

Hi blom,

i am working on a windows platform and the database is sqlserver. this is what exactly i have to do

Only source rows for the current fiscal year are to be exported to an Excel-readable format every night. There will only be 1 file per fiscal year – that is the file will be overwritten each night until the fiscal year changes (at which time a new file, with the new fiscal year as part of the filename, is created).

can u help me out with this, as i dont know how to script this

 

[sashu12]

what i have done is, i have created a parameter file for suppliying the values for fiscal year start date and fiscal year end date. all i need to know is

1. How can i overwrite the file every night until the fiscal year changes
2. Once the fiscal year changes, how can i create a new file with the yearsname and repeat the process

can u help me with creating a script to run this

thanks

 

[blom0344]

I understand your cause, but I do not understand why you simply do not use Powercenter (as this is an INFA forum)
to solve your issue.
The only thing you need is a router in your mapping to steer the output to a certain target. You can define a whole set of targets (as file) and give them proper name.
With 100% INFA logic you can accomplish what you need (without needing batch files or cmd files etc)

Ties Blom

 

[sashu12]

Hi blom, well i have created a router, and the mapping is complete, the data is getting loaded into the target correctly. and i have set the parameters for the start and end date.

the problem is i have to automatize the creation of new file with the name of that particular fiscal year.

It should be like i am loading the data today and i should check whether, the new fiscal year has come or not. if it is new year then a new file should be created automatically and the data for that day loaded into new file.


for example if the current fiscal year is 2007, i should be able to create a file name as FY2007 automatically

I think I am clear about what i am saying

 

[blom0344]

You specify the name of the file where data is written to in the session properties. Even if your target reads FY, then you can simply overwrite the file name in the session to anything and anywhere you like.

Ties Blom

 

[ArtieChoke]

Why don't you create empty files for each year for 30 years (if you expect it to last that long!). Then you can use your parameter logic to determine the year change and select the proper file name.

Ties, how would you update a parameter file using just infa?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[blom0344]

Not, I guess
I always managed to avoid them somehow...

Ties Blom

 

[sashu12]

hey artie,

well, first of all, i dont know how many years they are going to require this and i am not able to determine how to check for a fiscal year change in a batch file. can u help me with the logic

thanks

 

[blom0344]

A parameter file may offer a solution, but you can build all of this within 1 mapping (no additional things needed)

Steps:

1. Prior to router establish the FY
2. Define router with as many outgoing sets as you need output files.
3. Define as many targets within the mapping as you need FY's
4. In session set targettype to file and adjust the path and name of each file

Ties Blom

 

[sashu12]

hey ties, thats a good suggestion, but i have to create a parameter file, as per the guidelines, coz i dont know, for how many years i am going to need this,

 

[ArtieChoke]

I think the only way to solve this is to write a program to update your session parameter file for the file name and to create the files. There's no way to do this in a batch command file.

There is a new windows batch command language that could do it, but it's got a learning curve since it's object oriented.

Another way that might work is to get a text editor that allows command line input so you could have it read a file and make a change, but you'd still have to get the current year - which you can do with the for command on the current date. See the command help for an example of that.

If you have VB 6.0, I wrote a program to update parameter files I could send you.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[ArtieChoke]

Doh! I just thought of a real easy way to do it - write to the same file name and create a batch command to rename that file to the proper year. Here's a sample that give it a full timestamp:

REM renames transfer file as <xferfile>_YYYYMMDDHHMMSS.txt
REM
REM -------------------------------------------------------------
REM
SET DT=%date%
SET TM=%time: =0%
SET HH=%TM:~0,2%
SET MM=%TM:~3,2%
SET SS=%TM:~6,2%
SET DTX=%DT:~10,4%%DT:~4,2%%DT:~7,2%%HH%%MM%%SS%
RENAME %1.txt %1_%dtx%.txt

You could probably just use the %DT:~10,4% to get the year part.


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[fearthellama]

If you are targeting a flat file then you can create a filename port on the target. Use an expression transformation to calculate the filename, changing it for each fiscal year.

You create the filename port while creating the target ports. Click the icon on the far right hand side on the "columns" tab's toolbar.

This works in PC 8.1.1