Ok - it appears the lookup transformation will work, I can ignore the error
that will stop the whole package when the lookup is not found. Fine - now I
can use a conditional split and pull all my null records to be sent to ole
destination.
Serious performance problem w/ lookup, before package even starts it goes to
lookup table, does a select * and caches entire table for lookup. Well, my 8
million record table brings the ssis server to it's knees. New problem now -
I will post new thread regarding this. Looks like all is solved w/ this
thread.
[quoted text, click to view] "Chris" wrote:
>
> In SQL 2005 SSIS - how would I be able to load data into a oledb destination
> table only if the record does not exist?
>
> I could load the data into a staging table then call a sproc to do an insert
> using a left join where dest_table.pk is null, however I prefer not to manage
> a staging table and want SSIS to first verify not exist, then load...
>
> any help much appreciated,
Hello Chris,
Chris see my earlier post re the memory and caching.
As for what to do with "no match was found"
You have a couple of options
In the error setup you can
Fail Component - Default - No match found and the component fails
Redirect Row - Any source rows not matched go dow the error flow
Ignore Error - Pump down the normal path all rows and will contain NULLs
where no match possible.
Allan
[quoted text, click to view] > Ok - it appears the lookup transformation will work, I can ignore the
> error that will stop the whole package when the lookup is not found.
> Fine - now I can use a conditional split and pull all my null records
> to be sent to ole destination.
>
> Serious performance problem w/ lookup, before package even starts it
> goes to lookup table, does a select * and caches entire table for
> lookup. Well, my 8 million record table brings the ssis server to it's
> knees. New problem now - I will post new thread regarding this. Looks
> like all is solved w/ this thread.
>
> "Chris" wrote:
>
>> In SQL 2005 SSIS - how would I be able to load data into a oledb
>> destination table only if the record does not exist?
>>
>> I could load the data into a staging table then call a sproc to do an
>> insert using a left join where dest_table.pk is null, however I
>> prefer not to manage a staging table and want SSIS to first verify
>> not exist, then load...
>>
>> any help much appreciated,
>> chris
