Designer Questions

dataservices job design
1

Hi,
I have a few questions:

  1. I have a source table with 5 columns A,B,C,D,E and a target table A,B,C,D,E,F out of which A,B,C,D,E are from source. When using query transform,is there any way in designer to automatically map the source to target columns based on names?..This would help me a lot in mapping because even for the staright mappings, i have to manually map each column in the transform.

  2. In my dimension, i have two columns ETL_LOAD_DATE and ETL_MOD_DATE. In my incremental load, i mapped ETL_LOAD_DATE to sysdate(). Is there any way to load sysdate() for new records and sysdate() for updated records??..

[/u]

shalini (BOB member since 2008-03-26)

2

You can delete columns out of your target and manually map a group of them across i.e. select several columns and drag from the source to the target. is that what you mean?

I assume you are using a table comparison to generate your inserts and updates, if so after your table comparison split your flow into 2 sections, use a map operation on both sections of the flow, in the first flow only allow inserts through and in the second flow only allow updates through, then you can modify the columns on each flow as you wish,

Hope this helps

davc4 :uk: (BOB member since 2009-07-03)

3

Same response as David, just using different wording

a) Drop the query onto the dataflow, connect it with the source BUT DO NOT CONNECT IT WITH THE TARGET. Now go into the query - it will not have the target table columns - and drag all A-E columns from left schema to right schema. Now connect the query to the target.
(If you connect a query that has no output schema to a target, we assume you want to load all fields and copy them into the query for your convenience.)

b) Don’t. Building such a dataflow takes too much time.

Really, it should work easier but it is one of the things DS has no good solution for, just workarounds. And usually it is not worth it. Having one column should be sufficient and if not, I would rather prefer using database triggers to fill the two values.

Werner Daehn :de: (BOB member since 2004-12-17)

4

A nice enhancement would be a map_column_operation widget… gives all the columns in the data stream, and you select for each one whether or not they should pass through inserts, updates, or both. You’d place this right before the target table.

Then the SQL to the target would get modified to only have some of the columns vs. others depending upon the state of the incoming data row.

dnewton :us: (BOB member since 2004-01-30)

5

Shalini, it’s better to start a new thread for a legitimately new topic.

I will split this off into its own topic.

dnewton :us: (BOB member since 2004-01-30)