SSIS Lookup data update

0
votes

I have created a SSIS Package that reads data from a CSV file and loads into table1 . the other data flow tasks does a look up on table 1 .Table1 has columns x , y, z, a ,b . Table 2 has columns a , b ,y,z Lookup is done based on columns y and z . Based on the column y and z , it is picking up a and b from table 1 and updating table 2 . The problem is the data gets updated but i get multiple rows of data thats is one without updation and one after updation .

I can provide more clear explanation if needed .

1
ssis
please provide the needed help experts! - Ritu
You definitely need a clearer explanation. What does 'one without updation and one with updation' mean? Perhaps you should learn to do this in an update statement instead of fiddling in SSIS - Nick.McDermaid
'one without updation and one with updation' mean means I have a data row Event_ID Model_Dsc Model_Cd Derivative_Cd VIN Length (cm) Width (cm) Height (cm) 1 L405 Range Rover T1 QVBW NULL NULL NULL NULL Afer Updation - Event_ID Model_Dsc Model_Cd Derivative_Cd VIN Length (cm) Width (cm) Height (cm) 5 L405 Range Rover T1 QVBW NULL 5199 2073 NULL Ideally I should only get 1 row , the second row . - Ritu
Describe how your package is performing the update. Aside from the CSV source, and the lookup, what other components are in your data flow? - Tab Alleman
I have 2 data flow tasks . Data flow task1 is reading a csv file and loading into table 1 .Data flow task2 is taking source as table2 then data conversion then lookup based on to columns Code and description . Based on the lookup , picking up values of length , width and height from table 1 and filling table 2 . I hope I am clear . Please appreciate your help. - Ritu

1 Answers

0
votes

Fleshing out Nick's suggestion, I would get rid of your second data flow (the one from Table 2 to Table 2).

After the first Dataflow that populates table 1, then just do an EXECUTE SQL task that performs an UPDATE on Table 2, and joins to Table 1 to get the new data.

EDIT in response to comment:

You need to use a WHERE clause that will match rows uniquely. Apparently Model_Cd is not a UNIQUE column in JLRMODEL_DIMS. If you cannot make the WHERE clause unique because of the relationship between the two tables, then you need to select either an aggregate [Length (cm)] like MIN(), MAX() etc, or you need to use TOP 1, so that you only get one row from the subquery.