February 6, 2012 at 9:08 am
Does anyone know if there's a new way to handle insert-else-update in an SSIS package in 2012?
We've started a deep eval of 2012 features for use in a re-design of our enterprise data warehouse. We're really exited about the possibilities of MDS, SSRS in Sharepoint integration mode, etc.
It looks like you'd still do an insert-else-update in SSIS the way we have been doing them with a lookup then split to either an OLE DB Destination (insert) or an OLE DB Command (update).
Thanks in advance
February 6, 2012 at 9:24 am
A recent article explains one approach that can be found here[/url].
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 6, 2012 at 4:22 pm
Cool, thanks for the idea! I can't say that I fully follow all the details on my first pass of reading that, although I do get the concept of dynamically generating a MERGE stmt in a stored proc.
I was hoping that there was now a merge transformation in 2012 even though I'm not seeing one that looks like that in the designer.
Oh well.
Thanks again!
February 7, 2012 at 2:55 am
Ted Zatopek (2/6/2012)
Cool, thanks for the idea! I can't say that I fully follow all the details on my first pass of reading that, although I do get the concept of dynamically generating a MERGE stmt in a stored proc.I was hoping that there was now a merge transformation in 2012 even though I'm not seeing one that looks like that in the designer.
Oh well.
Thanks again!
If you are allowed to install 3rd party components, this is what you've been looking for:
SSIS Dimension Merge SCD Component [/url]
The built-in SCD wizard in SSIS does roughly the same ... but slower.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 9:40 am
The above component is fast, but it does require significantly more memory to do it that way.
March 14, 2012 at 4:55 am
It maybe worth evaluating the new CDC components within SSIS. CDC has been added at both the Control flow and data flow levels.
March 14, 2012 at 4:58 am
Mark Fitzgerald-331224 (3/14/2012)
It maybe worth evaluating the new CDC components within SSIS. CDC has been added at both the Control flow and data flow levels.
If SSIS 2012 isn't an option yet:
CDC is also available in earlier versions, you just have to write the queries yourself 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2012 at 4:49 am
Ted Zatopek (2/6/2012)
Does anyone know if there's a new way to handle insert-else-update in an SSIS package in 2012?We've started a deep eval of 2012 features for use in a re-design of our enterprise data warehouse. We're really exited about the possibilities of MDS, SSRS in Sharepoint integration mode, etc.
It looks like you'd still do an insert-else-update in SSIS the way we have been doing them with a lookup then split to either an OLE DB Destination (insert) or an OLE DB Command (update).
Thanks in advance
As Koen said,if you are allowed you can use third party tool from any one of these
" Table Difference component or Dimension Merge SCD component , TF upsert Destination component "
Their performance is very excellent...
Thanks,
Charmer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply