Updating data

Question

Updating data

Dharmendra K Pandey

SSC Enthusiast

Points: 149
More actions
March 23, 2006 at 3:02 am

#168790

Hi All!
I am new to SSIS. Could someone suggest me the SSIS analog of DD_UPDATE that we use in Informatica.

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

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Can you expand on what DD_UPDATE is? I have a vague recollection of it from my INFA days of 2 and a half years ago...but can't properly remember.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Dharmendra K Pandey SSC Enthusiast Points: 149 More actions · Answer 2

There is a transformation in Informatica named Update Strategy. It has three distinct options to deal with the incoming records based on some user defined conditions. The three options are:

DD_INSERT: It inserts the incoming records in to the destination table as new records if the condition is satisfied
DD_REJECT: It rejects the records if the condition is not satisfied
DD_UPDATE: It updates the records in the destination table with the incoming records if the condition is satisfied.

I am aiming to update or insert new records into the destination table based on whether the incoming records are new records or updated records. SSIS provides the option of using the SCD transformation to attain it. But I am interested in achieving it through other transformations.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

AHA. Yes, it all comes flooding back now.

Here is how you deal with this in SSIS: http://www.sqlis.com/default.aspx?311 There are 2 methods here - I recommend you use method 2.

Its a bit different to how you do it using the "Update Strategy" transform so try not to look at the SSIS method with your "Informatica head" on ...if you know what I mean.

Hope that helps.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Dharmendra K Pandey SSC Enthusiast Points: 149 More actions · Answer 4

Hi Jamie!!

Thanx a ton.....

I had tried the second method earlier but was somehow not convinced of its elegance. The first method suggested in the link sent by you was just what I was looking for.

Dharam

Dharmendra K Pandey SSC Enthusiast Points: 149 More actions · Answer 5

Hey Jamie!!

I guess my response was a bit premature coz the issues haven't settled in totto. The suggested method indeed does help in inserting new data. However the issue of updating the data already present in the destination table still stands unresolved. I would appreciate if you could help me in this regard

Dharam

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

The OLE DB Command transform is used to do UPDATEs from the pipeline.

Unfortunately it suffers from a problem that I am told INFA has as well, UPDATEs are issued a row at a time so on a large dataset this can be very unperformant. In this case, load the data into an interim table and issue a set based UPDATE using Execute SQL Task. Ashvini Sharma has blogged about this issue.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson