Source and Target columns

[DSmamta]

Hello All:

I have a source file that has 17 columns and a target file that has 25 columns.

In the transfomer I match the 17 columns into respective columns in the targets 25 columns?

How do I take care of the rest 8 columns? They show up Red in the transformer since they are not present in the source file. They need the 8 columns in the target.

Thanks.

MJ

[tonystark622]

In the Derivation, put whatever you want in the output. e.g. "abc" for the value abc; "" for a non-null empty string. You can also use @Null if those columns in the target are nullable.

Good luck!
Tony

[vmcburney]

You can either link input columns to more then one output column or you can set output columns to null values by typing @NULL or 0 or " " into the derivation field for those empty columns or you can use a default value such as the current date time or you can find extra values by doing a lookup. This is more a business decision then a DataStage decision.

[ray.wurlod]

Consult your planning (source to target mapping or target from source mapping) document.

You DO have a plan, don't you?

Based on the business rules specified in this plan, fill in derivation expressions for each of the 25 target columns. They may be based on any combination of input column values, system variables, stage variables and all the other operands presented to you by the Expression Editor.

[ranga1970]

Though The columns might be present in the Out put, you just Ignore them by not selecting corresponding data in the output stage, that means you are leaving them as null values by not loading any data.

[rasi]

As Ray said look for the source to target mapping document. Why you need to have 8 extra columns in the target with nothing. Ask the data modeller and if not going to be used remove it from the target. Keeping extra unwanted columns is additional burden to the database and etl jobs.


Thanks

[changming]

do not give null value to he rest of column, it will cause problem in the future.

[DSmamta]

Hello All:

Thank you for all your responses.

I guess I had better explain why the target has more columns than the source. Well the target will be getting data from other sources and my job is to concentrate on 4 sources see that its data is extracted transformed and loaded to this target.

The thing is there is no documentation done yet (atleast not properly) and wanted advice from you guys (my gurus... :D ) and then go and approach these guys here.

I have been able to resolve this issue by plugging in @Null since these fields are set to Nullable in the table definitions. But still I will now go ahead and get it cleared.

Thanks once again, really appreciate all your guidence and help.

MJ

[ray.wurlod]

It's perfectly OK to have more, or even fewer, columns in the target than in source.

If you don't want to load the other 8 columns in this job, you can omit them completely from the job design that inserts the rows (provided the omitted columns are equipped with suitable default values in the table definition).

In a subsequent job you can update these rows, providing values for the columns not yet loaded. You just have to be able to identify the unique key values for the rows.