July 28, 2011 at 2:31 am
Hi,
I have created a new Dataset and I want to use the primary key from that dataset to delete from a table before inserting all the data into that said table.
So I am okay with creating the datasource and inserting into a new data destination. But I am struggling with the delete bit in between. Can anyone help?
Basically all I am trying to do is a 2008 merge but this is 2005 maybe I am going around it wrong the only way I can think of doing it is to delete from the source what would be the updates and just insert everything again.
Many thanks
July 28, 2011 at 2:36 am
There are a few ways of doing this,
one way is to write an update or delete statement in the ole destination and use this in the dataflow, though this means that your updates will be row-by-row.
another way which I perfer is to create a staging table on the same database put all your updates or deletes in there and do the update with t-sql using a join between the tables.
July 28, 2011 at 2:50 am
steveb. (7/28/2011)
There are a few ways of doing this,one way is to write an update or delete statement in the ole destination and use this in the dataflow, though this means that your updates will be row-by-row.
another way which I perfer is to create a staging table on the same database put all your updates or deletes in there and do the update with t-sql using a join between the tables.
I second this approach.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 28, 2011 at 2:52 am
I am interested in this subject too, could you please post a code sample.
Thank you,
Iulian
July 28, 2011 at 2:52 am
steveb. (7/28/2011)
There are a few ways of doing this,one way is to write an update or delete statement in the ole destination and use this in the dataflow, though this means that your updates will be row-by-row.
another way which I perfer is to create a staging table on the same database put all your updates or deletes in there and do the update with t-sql using a join between the tables.
Hi thanks I have a lot of updates inserts so I want to avoid a row by row. What I am trying to do is find the most efficient way of doing this. My way is :-
1)creating RECORD SET A by selecting from source A
2) then deleting everything in Destination A which is in RECORD SET A (how do I do this in SSIS dataflow)
3)inserting RECORD SET A into Destination A
I am not sure I understand your staging table?
1)creating RECORD SET A
2)Insert RECORD SET A into staging table
3)Update join staging table and destination?
4)Insert join staging table and destination?
Would that be as efficient?
July 28, 2011 at 3:01 am
Edward-445599 (7/28/2011)
steveb. (7/28/2011)
There are a few ways of doing this,one way is to write an update or delete statement in the ole destination and use this in the dataflow, though this means that your updates will be row-by-row.
another way which I perfer is to create a staging table on the same database put all your updates or deletes in there and do the update with t-sql using a join between the tables.
Hi thanks I have a lot of updates inserts so I want to avoid a row by row. What I am trying to do is find the most efficient way of doing this. My way is :-
1)creating RECORD SET A by selecting from source A
2) then deleting everything in Destination A which is in RECORD SET A (how do I do this in SSIS dataflow)
3)inserting RECORD SET A into Destination A
I am not sure I understand your staging table?
1)creating RECORD SET A
2)Insert RECORD SET A into staging table
3)Update join staging table and destination?
4)Insert join staging table and destination?
Would that be as efficient?
SSIS is very fast at inserts, but slow at updates.
So one of the most efficient ways of doing this is to split the data in your SSIS pipeline - the inserts go into the destination table and the updates are inserted into another staging table. The final step of the process is a single Execute SQL task which runs an update from the staging table to the destination table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 28, 2011 at 3:03 am
Yes pretty much the second set of steps you outlined.
Insert data into a staging table,
then do a set based update based upon the primary key that joins them,
this is easily the most efficent in terms of processing time as the update can be processed as set.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply