tag:blogger.com,1999:blog-17332816.post9051800178316087022..comments2023-04-05T22:38:04.960+08:00Comments on Cup(Of T): Easy Upserts in SSIS 2005piers7http://www.blogger.com/profile/11186470645521299750noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-17332816.post-53710916552979630462019-03-20T20:21:40.664+08:002019-03-20T20:21:40.664+08:00Until now I have always felt that SSIS operations ...Until now I have always felt that SSIS operations are a bit complicated and especially the Upsert operations but I want to understand that what role do triggers play in the Upsert operations.I am right now reading this post to get some information but still unable to understand from it. https://zappysys.com/products/ssis-powerpack/ssis-upsert-destination/ James Zicrovhttps://www.blogger.com/profile/00094356083987556970noreply@blogger.comtag:blogger.com,1999:blog-17332816.post-10249515214395871002010-04-29T20:58:38.957+08:002010-04-29T20:58:38.957+08:00Well, under the covers that's what happens wit...Well, under the covers that's what happens with an INSTEAD OF trigger right: the set-based bit just happens from a temporary table in TempDB.<br /><br />But there are lots of advantages to using a staging table other than just that, principally not having to enroll the source system in a distributed transation but still achieve atomicity.<br /><br />But I still think it's a really nice technique sometimes. I'm working on a version that handles SCD type 2 as well...piers7https://www.blogger.com/profile/11186470645521299750noreply@blogger.comtag:blogger.com,1999:blog-17332816.post-32338297752422636802010-04-27T21:34:12.148+08:002010-04-27T21:34:12.148+08:00Piers,
Have a look at this video on http://www.sq...Piers,<br /><br />Have a look at this video on http://www.sqlshare.com/Player.aspx?vid=71&plid=&searchid= <br /><br />The option demo-ed here suggests loading the data for an update into a staging table and then doing a set based update. The numbers seem to scale really well.<br /><br /><br />Interesting, let me know what you think.<br /><br />SanjaySanjay Gonsalvesnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-20066168574353612532009-10-03T02:47:50.362+08:002009-10-03T02:47:50.362+08:00I don't know if Nicholas ever tried this out, ...I don't know if Nicholas ever tried this out, but I can report that this works very well for the moderate amounts of data in the package I initially tried it withAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-60401625450590580092009-10-03T01:53:14.046+08:002009-10-03T01:53:14.046+08:00Never mind, I found it. It seems it has to be type...Never mind, I found it. It seems it has to be typed into the FastLoadOptions textbox in the editor windowAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-86321934301324451652009-10-03T01:46:57.370+08:002009-10-03T01:46:57.370+08:00I can't find a FIRE_TRIGGERS property in the O...I can't find a FIRE_TRIGGERS property in the OLE DB destination. Does that need to be set programmatically?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-53575195837653020342009-10-03T01:45:43.430+08:002009-10-03T01:45:43.430+08:00No, that's not my point. If you insert the row...No, that's not my point. If you insert the rows from inserted that don't exist in the destination, then when you call the UPDATE statement, the rows in destination that correspond to the rows in inserted will be update, in other words, all the rows will be updated. If you do it the other way around, then only the rows in the destination that exist before the insert will be updated.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-82351999664754168812009-10-01T07:36:00.289+08:002009-10-01T07:36:00.289+08:00Maybe. Normally the key in use would guarantee uni...Maybe. Normally the key in use would guarantee uniqueness, even if it wasn't the PK, so it wouldn't be an issue. If the key isn't unique you have bigger problems.piers7https://www.blogger.com/profile/11186470645521299750noreply@blogger.comtag:blogger.com,1999:blog-17332816.post-67854455189956728112009-10-01T04:08:52.241+08:002009-10-01T04:08:52.241+08:00Oops ... that makes no sense. This: "Shouldn&...Oops ... that makes no sense. This: "Shouldn't the UPDATE be done before the INSERT so you are only updating the records in the destination?" should have been this:<br />"Shouldn't the UPDATE be done before the INSERT so you are only updating the records that exist in the destination prior to the INSERT?"Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-55852323864068504872009-10-01T03:47:15.858+08:002009-10-01T03:47:15.858+08:00Shouldn't the UPDATE be done before the INSERT...Shouldn't the UPDATE be done before the INSERT so you are only updating the records in the destination? After the insertion, all the records in inserted will be in the destination table, n'est-ce pas?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-17332816.post-52414936244875424752009-03-18T03:45:00.000+09:002009-03-18T03:45:00.000+09:00Looks very promising, I think I will try that out ...Looks very promising, I think I will try that out on our next project! Thank you!Nicolas Gallerhttps://www.blogger.com/profile/09553414029059795270noreply@blogger.com