Dynamically create as many files as values in a column

[jerome_rajan]

Hi,

I came across this interesting requirement. Pardon me if this has already been discussed but I couldn't find it anywhere.

I have a source table with columns:

Name, Age, Location

The Location column can contain any value and is not fixed. For e.g. It may contain New York. it may also contain US. So it can be a city, state, country or even a district.

The requirement is simply to load records belonging to different locations to different files. So if I have 20 locations, the job would have to determine the number of values in Location at runtime and create as many files.

I have devised a logic to implement this using a UNIX script and a looped sequencer. But I was wondering if there's an easier way to implement this.

Help Appreciated

[chulett]

Off the top of my head, a Server job (or Server Shared Container) writing to the Folder stage or using the UNIX csplit function after the file has been written could do this.

[jerome_rajan]

I'm not sure if I'd want to use server components in my parallel job. Will look into the UNIX command. Thanks you!

[chulett]

In the immortal words of Blue Oyster Cult: Don't Fear the Server.

[jerome_rajan]

ha...i am not yet a seasoned campaigner to justify the presence of a server component in my jobs!

[vamsi.4a6]

Can you explain the logic in simple steps how you implmented the above requirement by using Looping and Unix?

[jerome_rajan]

1. Create a single job with a source connector that extracts data for one location. This condition should be parametrized (e.g. WHERE LOCATION=#jp_Location_Nm#).

2. Implement your logic and create a file where again the file's name should be parametrized (e.g. File Name= Target_#jp_Location_Nm#).

3. Now create a looped sequencer with one UNIX script out of the loop. The UNIX script should execute a query against the DB to extract a unique list of all locations and store it in a file (delimited)

4. The file created in the above step gets passed in the Start Loop activity which will pick up one location at a time from the file and pass it to the parameter #jp_Location_Nm#.

5. The sequencer loop gets executed as many times as there are locations in the file and a separate file gets created for each location.

I know that this logic sounds complicated but is actually pretty easy to implement. There might be an easier way out but I can't think of any.