Need help on a mapping

Get new comments by email
You can cancel email alerts at anytime.
Comments (2)

Nath in Oakland, California

84 months ago

Hi All,

I am a Informatica learner. I was working on SQL Server DTS and SSIS ETL Tools before.

I was thinking about a scenario:

Say we have a "Customer" table in Source with Cust_ID, Lst Name , Frst Name ,DOB, Created Date and Updated Date.

Created Date tells when a record was created and Updated Date helps to find whether a record is updated later.These 2 columns are maintained for ETL purposes by application.

Daily night ETL runs and it must insert new records created on that day in Source to Target and also Update Updated Records in Target.

As I am a learner, Can you someone tell me how a mapping can be created for this scenario for this table and ETL must look the dates "from Last ETL Run Date to Excution Date".

Please Guide me.

Thanks,
Nath

- Was this comment helpful? Yes / No Reply - Report abuse

priya in Hackensack, New Jersey

82 months ago

Nath in Oakland, California said: Hi All,

I am a Informatica learner. I was working on SQL Server DTS and SSIS ETL Tools before.

I was thinking about a scenario:

Say we have a "Customer" table in Source with Cust_ID, Lst Name , Frst Name ,DOB, Created Date and Updated Date.

Created Date tells when a record was created and Updated Date helps to find whether a record is updated later.These 2 columns are maintained for ETL purposes by application.

Daily night ETL runs and it must insert new records created on that day in Source to Target and also Update Updated Records in Target.

As I am a learner, Can you someone tell me how a mapping can be created for this scenario for this table and ETL must look the dates "from Last ETL Run Date to Excution Date".

Please Guide me.

Thanks,
Nath

Well,
For this you can use dynamic lookup.

1.drag your source
2. Take lookup on Target
3. In Lookup Propertiec tab
check DYNAMIC LOOKUP CACHE
ALSO CHECK INSERT ELSE UPDATE

4. Now Drag Columns from source to your lookup.
5.Again goto lukup properties, In condition tab
ADD condition cust id = cust id1
6.Now goto Ports tab:
You will get one new port NEW LOOKUPROW
Also every target is associated with the source in associated port that comes automatically.
7.NOW take a FILTER Tx ..Pass all the Trargt Columns + Newlukup row to Filter
8.Edit Filter Tx ..Give condition Newlookuprow =1 (i.eINSERT)
9.Now take update stragy Tx.Pass all to US Tx except Newlukuprow.
10. Now take your target and link all to your target.

Follow steps 7 to 10 with one more filter just give newlukuprow =2 there i.e UPDATE

Now SAVE and run

- Was this comment helpful? Yes (1) / No (1) Reply - Report abuse

» Sign in or create an account to comment on this topic.