CODEWITHSUNDEEP
ssis
kanth
kanth

Reputation: 341

Compare two source and update sql server table in SSIS?

I have excel source and sql server table .

Excel Source column is 

 Mno    Price1  Price2
 111     10      20
 222     30      25
 333     40      30
 444      34      09
 555      23      abc

Sql server Table

Product table name

  PId    Mno    Sprice   BPrice
    1      111    3        50
    2      222    14        23
    3      444    32       34
    4      555    43       45  
    5      666    21       67

I want to compare excel source Mno(Model number) with sql server Product table Mno (Model number), and if it is same i want to update Sql server Product table SPrice and Bprice.

Please tell me what are the steps i want to do?

I want to validate that excel sheet also, because in excel Price2 column have string values

if it's string value i want to send mail which row data are wrong.

I am new for SSIS so please give me details.

Upvotes: 0

Views: 5254

Answers (3)

cairnz
cairnz

Reputation: 3957

Read your new data in a source, use a lookup component for existing data. Direct row matches to a oledb command for update, and a destination for your non-matches for inserts (if you want to enter new products).

Upvotes: 1

Tom H
Tom H

Reputation: 47444

You can use a Merge Join Transformation with a full outer join (remember to sort your datasets before they input to the Merge Join Transformation), then have the output go to a Conditional Split Transformation. The Conditional Split Transformation can determine whether or not a row needs to be updated, inserted, or deleted and direct the flow to the appropriate transform to do that.

This was off the top of my head, and there may be a simpler transform for this. I haven't had the opportunity to work with SSIS in almost a year, so I might be getting a bit rusty.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Personally I think the simplest way to do this is to use a dataflow to bring the excel file into a staging table and do any clean up if need be. Then as the next step inmteh control flow have an Execute SQl task that does the update. Or if you need either an update or an insert if the record is new, use a Merge statement in the Execute SQl task.

Upvotes: 0

Related Questions