Post reply

Update Strategy

  • July 15, 2005 at 6:41 am

    #164999

    Apart from using the script component and SCD, isn't there any direct way by which the update strategy of the destination table can be changed? the default is insert. can't it be changed to "update" or "clear and insert"?Although an sql query can also be written in the destination,i want to knw if it be done directly as in data stage or informatica?

  • July 17, 2005 at 11:55 am

    #575118

    As Jamie Thomson found out, comparing SSIS to Informatica is definitely not an apples to apples comparison.

    http://blogs.conchango.com/jamiethomson/archive/2005/03/22/1172.aspx

    Ash blogged about upserting in SSIS here:

    http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx

     

    HTH

  • July 18, 2005 at 5:16 am

    #575160

    Sunil,

    You can write an Update statement in an OLE-DB Command component but this does row-based updates rather than set-based updates (i.e. an UPDATE statement is issued for every row that needs updating). Your other option is to load an interim table and then do a set-based update using the Execute SQL Task.

    Unfortunately there is as yet no equivalent of Informatica's Update Strategy transformation. I have to hold my hands up and say I don't know if this transformation allows Informatica to do set-based updates from the pipeline or whether they are row-based like SSIS and the OLE DB Command component. Perhaps you can tell me?

    I dare say an SSIS component that can do set-based updates in the pipeline is high on the list of priorities for the next version.

    Incidentally, look out for a whitepaper coming out imminently that provides a feature-by-feature comparison of Informatica and SSIS. I saw a PDF version of the paper just a few minutes ago so there are a few i's to be dotted and t's to be crossed (marketing types need to write the press release etc...) before it is out in the public domain. I'll announce the paper's availability on my blog (see the link below).

    -Jamie

     

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

  • July 21, 2005 at 11:16 pm

    #576301

    Thanks so much for your help. hope they do add a component which will directly provide an update option.

Viewing 4 posts - 1 through 3 (of 3 total)

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