Row yielded no match during lookup

I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.

Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.

Or else any other way to send an email after generating the text file ocntaining the non matching rows.

Please suggest using steps or example

There's likely to be more than one way to do this. Here' one possiblity. After the Dataflow task in the Control Flow, use a sequence container that contains two tasks, a script task that checks for the existence of the error file. Here's the script to check setting a Boolean variable, FileExists, to either two of false.

Dim objFile AsNew FileInfo(Dts.Variables("TempFileName").Value.ToString)

Dts.Variables("FileExists").Value = objFile.Exists

The second task in the container is the Send Mail task which is connected by a Precedence contraint which only executes if the FileExists variable is true.

HTH

|||

Thanks for reply.

Is there any way to invoke the send email task (Assume that the send email task) is present in eventhandler, when we transfer the non matching rows to flat file using flat file destination.

Please suggest.

|||

I'm not sure I understand what you're trying to do. I think you're expecting the OnError event of the DataFlow task to be triggered when a match is not found in the Lookup component. However, if you specify to redirect rows on the Lookup component when no matching row is found, the OnError event of the DataFlow task will not be fired. Am I guessing correctly? If not, which Event (there are several) are you planning on using the SendMail task. OnPostExecute of the DataFlow task? The two tasks used in the Sequence container above could be used in this event to obtain the same result.

|||

thanks for reply.

Your guess is right.

All I need is when lookup transform encounter no match then it should record all such distinct nonmatching rows and then send the same detail to other member using email.

I am new in SSIS, Please suggest me how to do this either by event or any other way

thanks

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.