Monday, March 26, 2012

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

No comments:

Post a Comment