ETL and Data Integration tools - Modern ETL software for everyone

Hi

I have set up a very simple transform, to take our 12 office locations from our ERP SQL db and push them to an office dimension.

The transform is set to Add New and Update Old Records.

I ran it once, and it pushed all the records. I then deleted all 12 records in the dimOffice table, and re-coded the transform to include an RTrim on the office name.

Now the transform runs, says it updates 12 records, but there is only 1 record in the table, instead of 12.

Information 7/28/2011 3:23:33 PM Starting Transformation...
Information 7/28/2011 3:23:33 PM Map Name: Load Offices
Information 7/28/2011 3:23:33 PM Author:
Information 7/28/2011 3:23:33 PM Version:
Information 7/28/2011 3:23:33 PM Description:
Information 7/28/2011 3:23:33 PM Preparing Writers...
Information 7/28/2011 3:23:33 PM Writer: {}, [Table=[cfrFacts].[dbo].[dimOffice]] Writer { } Is Ready
Information 7/28/2011 3:23:33 PM All Writers are Ready
Information 7/28/2011 3:23:33 PM Processing Data...
Information 7/28/2011 3:23:33 PM Reader:{} Reading all specified tables
Information 7/28/2011 3:23:33 PM Reader:{} Source Table: [TTC].[dbo].[SVC00902]
Information 7/28/2011 3:23:33 PM Reader:{} Data Source has: 12 Record(s)
Information 7/28/2011 3:23:33 PM Reader:{} Using Bulk Fetch
Information 7/28/2011 3:23:34 PM Reader:{} Read: 12 Records/Lines/Messages (in total)
Information 7/28/2011 3:23:34 PM Reader:{} Records/Lines/Messages per second: 96
Information 7/28/2011 3:23:34 PM Reader:{} Rejected: 0 Record(s)
Information 7/28/2011 3:23:34 PM Reader:{} Time Taken: 00:00:00
Information 7/28/2011 3:23:34 PM Writer: {}, [Table=[cfrFacts].[dbo].[dimOffice]] Inserted : 1 Record(s)
Information 7/28/2011 3:23:34 PM Writer: {}, [Table=[cfrFacts].[dbo].[dimOffice]] Updated : 11 Record(s)
Information 7/28/2011 3:23:34 PM Writer: {}, [Table=[cfrFacts].[dbo].[dimOffice]] Deleted : 0 Record(s)
Information 7/28/2011 3:23:34 PM Writer: {}, [Table=[cfrFacts].[dbo].[dimOffice]] Errors : 0
Information 7/28/2011 3:23:34 PM Transformation Completed

Jeff

It is probably because it has updated same record 12 times?

12 times=12 records

Mike

Why would it not insert the missing records?

And that’s not what the log says, the log says it UPDATED 11 records.

Jeff

This is probably because you have not specified the update key.

Hove a look at the documentation chapter add new and update records

Mike

Got it it works now

Jeff

Well update key is the key to updating data

Mike