Dynamically renaming variables

[Offshored2002]

Is there any way to dynamically rename variables based on a lookup in DS (I am a relative newbie):

For example:
A file is read in, and contains fields A, B, and C. They need to be renamed to D, E, and F (no change to type) respectively. We have a table derived from a database which has this information:
Old Field name: A New Field Name: D Load to table: TABLE1

Is there some way to dynamically rename the fields on output from a transform or modify stage?

[ArndW]

I think you might be able to leverage RCP and shared containers to do this. Declare your shard container with no columns and pass the original column name and new name as parameters to this container. Then use a modify stage to rename the column dynamically; repeat this call as often as necessary to process all the columns.

[sud]

Another strategy:

Since you are concerned about field names, I will assume that you are about to insert the data into database. In that case, you can create a job that reads the old to new column mapping from the table and create a insert/update statement which you can pass as a parameter to the job where you do the actual data transformation and have the old filenames in the metadata and the insert/update statement will take care of the mapping. A sample SQL could be : insert into <table>(newcol1,newcol2) values(orchestrate.old1,orchestrate.old2).

In case you are writing only to a file, probably the column names go into the header record and that can be very easy to generate as well.

[Offshored2002]

Sud, you are exactly correct in what we are trying to do:

1. Read in XML file which has the data to be loaded, with the "old" names (tags).
2. Update database with has the translated names which correspond to the XML tags (names) used which are in a lookup table which is pre-built.

No data manipulation is needed as of now and we don't want to manually code anything should any changes to field definitions occur.

Will post back with the solution used. Any other ideas welcomed. Thanks for the quick response to my 1st DSXchange post.

[sud]

Cool and welcome to dsxchange !!

[ray.wurlod]

I would use a Copy stage rather than Modify or Transformer, unless I had other processing to do on the data. Copy stage is very efficient - all it does is transfer from input to output.

[Ronnie]

Hello Forum members,

I have a similar requirement at work to dynamically rename columns. I have a couple of questions based off of ArndW's response:
1. Within the Modify stage, would the Specification be coded with one old-to-new column name at a time? So if I have 500+ columns, the container will have to be called 500 times, correct? I take it I would have to prepare my list of new dynamic column names ahead of time as a parameter string.
2. How do I incorporate the shared container within the job below? Do I drag it between the XML Input and Modify stage below?
Right now I have the job set up as:
External Source-->XML Input-->Modify-->DataSet
3. Is there some type of looping that must occur in order to dynamically rename all of the columns? I assume this because of the phrase "repeat this call as often as necessary to process all the columns". I would think the renaming has to be done prior to reading the first record. How would this looping be done?

Thanks

[ray.wurlod]

1. Yes. One at a time.
2. Shared containers are emplaced like any other stage. In addition, they may have parameters of their own, and you may need to resolve link names (there is a command button for this).
3. The only loop is the manual one when you're setting up the renaming. When DataStage executes any "pass through" column (even if it is being renamed) consists of two pointers to the same element of a structure - therefore there is no loop nor even any processing.

[Ronnie]

I'm sorry. I hate to say this but I need further clarification on how each element will dynamically be passed one at a time, up to 500+, prior to reading the file.

Do I manually take each old-new column and pass it to the container as a parameter, one at a time up to 500+ times as a maintenance effort prior to running my current job listed below? What makes me say this is the reply from Ray (bullet 3 from previous post) where the "manual loop to set up the renaming".

Otherwise, I can't see how renaming columns dynamically while reading records during run-time execution by placing a shared container within the job will work. But if so, then am I correct in the proposed job below (ArndW suggested to use Shared Container plus Modify stage)?

CURRENT JOB: External Source-->XML Input-->DataSet
PROPOSED JOB: External Source-->XML Input-->Shared Container-->Modify-->DataSet

Thanks so much for your patience.