updating and replacing keys with names using lookups in ssis

Question

updating and replacing keys with names using lookups in ssis

rohit3556

SSC Rookie

Points: 39
More actions
February 1, 2012 at 9:58 pm

#150771

thanks in advance ,
i have a flat file having 10000 records with column having foreign keys.
i m doing a look up only on 1 foreign key
in result i want a table in which that foreign key was replaced by names referring to that key from other tables.
how is it possible ..??

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

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

rohit3556 (2/1/2012)
in result i want a table in which that foreign key was replaced by names referring to that key from other tables.

What do you mean exactly with that?

Can you give sample data and desired output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rohit3556 SSC Rookie Points: 39 More actions · Answer 2

my flat file contains a column name as Insurer-Dimension-key which is foreign key to the table insurer_Dim

now i am doing look up using this Insurer_Dim

in final destination, instead of keys i want names from the insurer_dim referring to that foreign keys.

how can i do it using look ups..?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

rohit3556 (2/2/2012)
my flat file contains a column name as Insurer-Dimension-key which is foreign key to the table insurer_Dim
now i am doing look up using this Insurer_Dim
in final destination, instead of keys i want names from the insurer_dim referring to that foreign keys.
how can i do it using look ups..?

Ahem. Sample data, preferred output.

There is a bit of a language barrier here, as the sentence "instead of keys i want names from the insurer_dim referring to that foreign keys" doesn't make much sense to me.

How can a name reference a foreign key?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rohit3556 SSC Rookie Points: 39 More actions · Answer 4

Sorry for Inconveniences

in detail....

i am having a flat file having column Insurer-Dimension-key

i am having a table "Insurer_Dim" having column Insurer Key , Insurer Short Name , where insurer key is the primary key and foreign key to the flat file also .

in destination

at place of Insurer-Dimension-key , i want the insurer Short Name at the place of Insurer_Dimension_Key

now in look ups i am using this Insurer_Dim table for referencing

i am using same flat file as source and destination

please help me out ,

thanks

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Ah OK.

I don't think it is possible to read and write from the same file at the same time. It would probably give interesting concurrency issues.

So first read the flat file and do a Lookup on the Insurer dimension. Get the short name column from that dimension and write that column to another flat file.

After this processing is done, simply rename the new flat file to the old flat file.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rohit3556 SSC Rookie Points: 39 More actions · Answer 6

How to get that Short Name column and write it to flat file.....?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

rohit3556 (2/2/2012)
How to get that Short Name column and write it to flat file.....?

Lookup Transformation

Google for SSIS lookup tutorial and you'll get lots of interesting links.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP