July 15, 2005 at 6:41 am
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
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
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
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