Look up transformation

Question

Look up transformation

surya-434952

SSC Eights!

Points: 952
More actions
October 21, 2009 at 12:10 am

#156338

HI,
I am struggling to do this. I am creating a sample package which will have csv file as input and i am loading it to a table. before loading to table i need to check whether a row is modified or not if modified i need to modify with input data and if that data is not present i need to insert.
I took one look up transformation , flat file source and conditional split
my idea is i will load lookup component with already existing table data and i want to compare this data with incoming flat file data using conditional split. Am i doing wrong?? Could you please guide me in this process.
Thanks,
Surya

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 1

Hi - I think you need to pass the data from the Flat file source straight into the lookup, then compare each row, any rows which match are the same so you can forget them.

Use the lookup to add one of the columns from the lookup table into your dataflow (columns view in the lookup <add as new column>)

Rows which don't match will be sent to the error output of the lookup - attach this to your conditional split & change teh behavior from "fail" to "redirect row" which you use to split rows into those with a value in the column you added to the data flow in the lookup & those with no value.

Rows with a value exist in the destination table so send these to an OLEDB Transfrom to perform an UPDATE.

Rows with no value in the looup table don;t exist so send these to an OLEDB destination to INSERT them into the table.

Remember if you are using Full caching in your lookup then lookups are CAse SeSitIve - you will need to use a character map transform to make a copy of columns in upper case & Upper your input as well.

I hope this makes some kind of sense!! Jamie Thompson probably explains it all better than me here:

http://consultingblogs.emc.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Cheers

S C Penguin

surya-434952 SSC Eights! Points: 952 More actions · Answer 2

Hi,

Thanks for your detailed explanation. I am able to create successfully a sample package. I have few more doubts. I will mention them below

1) Is it possible to send multiple columns out put from conditional split

2) Is loading data into fact tables in a star schema dataware house differs to nomal ETL process

Thanks,

Surya

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 3

1) Is it possible to send multiple columns out put from conditional split

The Conditional Split will pass all columns through - it just sends different rows via different routes.

2) Is loading data into fact tables in a star schema dataware house differs to nomal ETL process

Not at all - in fact that is probably more the "normal" process (it's what I use it for anyway!)

I hope I understood your questions properly!

S C Penguin

surya-434952 SSC Eights! Points: 952 More actions · Answer 4

Hi,

Would you like to explain what it is meant by

a) different rows via different routes

b) columns through

c) normal process

Thanks,

Surya

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 5

a) different rows via different routes

A conditional split will produce more than one output & you can then perform different tasks on each output e.g. you may use the conditional split to separate rows based on a Country column, then you could insert rows with a country of "UK" into one table & insert rows with a country of "US" to another table.

b) columns through

All I'm saying is that the data isn't changed by the conditional split - all the columns & rows going into a conditional split will also be outputed from it.

c) normal process

You mentioned your Star schema as if it was not a "normal" process - I was tryingto make the point that loading a Warehosue Star schema is a perfectly normal use of SSIS & Lookups.

I hope I am making some sense!

surya-434952 SSC Eights! Points: 952 More actions · Answer 6

Hi,

First of all thanks your detailed reply and your kind coordination

sample data

Emp,Dept,EmpCode,join_date

surya,Admin1,A0012, 12.12.2008

rakesh,HR,T001, 10.12.2008

shree, Finance, F001,

shan,Tech,H002, 5.12.2007

as you said i am connecting flat file to look up transformation, i am referencing a table as source

which was in database filtering data using primary key

for example emp in the above data

now i want to update only changed data

for suppose empcode and join_date

i am connecting conditional split to script component

in script component i am writing query for updating table data with changed column data

Could you please guide me how to accomplish this

Thanks in advance,

Surya

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 7

If you want to update data in a dataflow you are probably better off with the "OLEDB Command" transformation rather than a script component.