November 28, 2011 at 10:06 am
Hi, We have around 90 packages in SSIS 2005, our target db is sql server 2005. Each table has an effective date and End date columns to specify the duration of the record. Package first inserts the records into table with end date as null. Then we have an execute sql task in which we are using below sql statement to update the end dates.
which works fine when there less data in the target table but takes a lot of time when the table gets huge.
sql query :
update STANDARD_COVERAGE set end_date= b.end_date
from STANDARD_COVERAGE a inner join (
SELECT c.sequence_id,c.production_source_key,COALESCE(MIN(c2.effective_date),CONVERT(DATETIME,'99991231')) AS end_date
from STANDARD_COVERAGE c
LEFT OUTER JOIN STANDARD_COVERAGE c2 ON c.production_source_key = c2.production_source_key
AND c.effective_date < c2.effective_date
GROUP BY c.production_source_key,c.sequence_id ) b
on a.production_source_key=b.production_source_key and a.sequence_id=b.sequence_id
if we use SCD transformation it will use OLEDB command which is not recommended since it hits the DB for each record.
Is there any other way to handle this or can we fine tune the above sql so that it'll not take much even though there are 1 million records in the target table.
Thank you
appreciate any help on this
November 28, 2011 at 12:43 pm
What I do is push all the Type 2 change records into a staging table rather than directly to the destination. Then using an execute SQL task to take the staging table I update the destination table to mark the older records as expired. Then using a separate execute SQL task I insert the new data from the staging table to the destination table.
If you are dealing with a lot of records you may find that using a staging table for your type 1 updates will be faster because it will be set based rather than record by record.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply