April 9, 2009 at 1:53 pm
I'm currently experiencing some blocking issues with "OpenRowset Using FastLoad". I think it's because I'm referencing the destination table in the Source object thusly:
SELECT
...
FROM
SomeLegitSource q
LEFT JOIN
MyBlockyTable m ON m.[someid] = q.[id]
WHERE
m.nonnullablecol IS NULL
... where "MyBlockyTable" is the destination table.
Taking a step back... what IS considered the Best Practice for this situation in SSIS 2005? Basically, how to best handle the age-old issue of "INSERT ... WHERE NOT EXISTS already"
I did some searching, but found myriad viewpoints and nothing definitive. SQL Server Destination object is not an option in our environment.
Thanks in advance SSC!
April 9, 2009 at 2:59 pm
The left join usually is the best way to handle that. It can also help in some cases to pull the data into a temp table, and then left join to that, in separate transactions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2009 at 9:08 pm
I agree with GSquared in that, if the UPDATE is the only thing you're doing, one or two SQL statements could be used for this.
If you need to include this as part of a larger ETL operation, you can use the Lookup Transformation. This will evaluate your in-memory results against a lookup table and will branch the output into records that were matched on the lookup condition and those that were unmatched (the latter of which will be sent to the Error Output). You could then send those errored (unmatched) rows to your destination table.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply