Tamizhan


Hi There,

I have a task to send mail when the source file contains null values against id column.

I have set conditional split to split the null value rows to the different destination.Please help me how do i achieve this task when the source file contains null values against id column.

Thanks in Advance.





Re: How do set send mail task?

Andy Leonard


Hi Savera,

I'm not sure I understand what you're trying to accomplish but I will take a guess:

If you are trying to fire a SendMail task when the rows containing nulls (redirected by the Conditional Split transformation) total more than zero rows;

  1. You could use a RowCount transformation to push the number of rows into a package-scoped variable (vnNullRowCount, for instance);
  2. Then you could configure a precendence constraint from the data flow to a SendMail task.
    1. The Precedence Constraint could be configured to use an expression, and that expression could be something like:

vnNullRowCount > 0

Hope this helps,
Andy






Re: How do set send mail task?

Savera

Hi Andy,

Many thanks for your response.Its really helped me to head start to the mail task.I have set variable for rowcount and i kept the expression as @[User::NullRowCount] > 0 in send mail task i tried evaluate this but it was false please let me know whats the problem with this.

Thanks in advance.







Re: How do set send mail task?

Phil Brammer

Savera wrote:

Hi Andy,

Many thanks for your response.Its really helped me to head start to the mail task.I have set variable for rowcount and i kept the expression as @[User::NullRowCount] > 0 in send mail task i tried evaluate this but it was false please let me know whats the problem with this.

Thanks in advance.



Are you doing this on the precedence constraint





Re: How do set send mail task?

Andy Leonard

Hi Savera,

The RowCount transformation uses a variable. This variable needs to be visible at the Data Flow Task scope and at the Control Flow scope, so make sure it is a package scoped variable. You create a package-scoped variable by right-clicking any unoccupied space in the Control Flow, and then selecting Variables. The variable has many configurable columns, but scope is not one of them - it is determined by the object last selected in the designspace.

After the variable is created with package-level scope, return the the RowCount transformation on the Data Flow and use this variable to count Null rows.

Next, on the Control Flow, create a Precedence Constraint between the Data Flow Task and the Send Mail Task. Right-click the constraint and select Edit. Change the Evaluation operation to Expression. In the Expression textbox, enter [your variable name] > 0.

Configure the SendMail Task to generate email to appropriate recipients. The SendMail task will be triggered if and only if the number of Null rows stored in your variable exceeds 0.

Hope this helps,
Andy





Re: How do set send mail task?

Savera

Hi Andy.

Many thanks for yor brief description.I followed all your steps.

1.Droped send mail task and right click add Precedence Constraint between the Data Flow Task and the Send Mail Task.

2.Created Variable with scope column by right clicking on control flow surface .

3.Same variable name has been created in rowcount trasformation.

4.wrote the expression as @[User::NullRowCount] > 0 still i am getting the same error sending mail fail(expression evaluation is False).

Please advice me anything else to be done .

Thanks.






Re: How do set send mail task?

Andy Leonard

Hi Savera,

In your step 3 you state you created the variable in the RowCount transformation You should have been able to use the variable created on the Control Flow.

Here's a demo of what I'm trying to say - perhaps the images will help:

1. On Control Flow, go to Variables.


Picture

2. Create a new variable named NullRowCount. Make sure the Scope is Package.


 

Picture

3. Add a data flow. Drag sources, a conditional split, and a RowCount transformation onto the Data Flow. Define an IsNull condition for the column you wish to catch and notify when NULL (I use AddressLine2 as the column in the example below). Name the Condition so you can connect to it later (I named the condition "Null Rows" in my example):


 

Picture

4. Connect the conditional split to the RowCount. When a dialog like the one below appears, select the Null Rows condition (or whatever you called it) in the Output dropdown:


 

Picture

5. Double-click the RowCount transformation to edit it. In Custom Properties, type (or select if you have SP2 installed) the name of the variable you defined in the second step above:


 

Picture

At this point, you are pushing the number of rows into the variable where the column contains a NULL. This is a count of the Nulls in that column.

6. Return to the Control Flow. Add a SendMail task. Click on the Data Flow task and connect a precendence constraint between the Data Flow and the SendMail tasks. Right-click the precendence constraint (the green arrow) and click Edit as shown below:


 

Picture

7. The Precedence Constraint editor opens. Change the Evaluation operation to Expression:


 

Picture

8. In the Expression textbox, type the @ symbol, the name of your variable created in step 2 and typed or selected (if you have SP2 installed) in step 5, followed by the evaluation portion (@NullRowCount > 0 in this case):


 

Picture

9. Click the OK Button to close the Precedence Constraint editor. If you hover over the fx graphic for the constraint, a tooltip will show you the expression. The constraint should be blue:


Picture

Hope this helps,
Andy





Re: How do set send mail task?

Savera

Hi Andy,

Thanks a lot for this wonderful demo.I am verycleared now after going thru these pictures and descriptions.

I have followed all your steps perfectly as said.

Eventhough i am getting the following error,

Progress: The SendMail task is initiated. - 0 percent complete
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.".
Progress: The SendMail task is completed. - 100 percent complete
Task Send Mail Task failed

On the SMTP Connection Manager I have tested with both an IP address and Server Name, and no authentication.

The Send Mail Task, it uses the above connection. The To: , From: , Subject fields are populated. Priority is Normal and no attachments and no expressions etc.

Please suggest me what could be the issue.

Thanks.






Re: How do set send mail task?

Phil Brammer

Quick test.

Open up a command prompt (Start->Run->cmd) on the box that you are executing this package on.

On the command line, type: "telnet server 25" (where server is your server/IP address of the e-mail box)

What happens If you get in, you can type "quit" and hit enter.







Re: How do set send mail task?

Savera

Hi Phil,

Thanks for the information.I tried in cmd i couldnt get .When i tried in run command i could able to access the mail server.

please suggest me to resolve this.

Thanks






Re: How do set send mail task?

Savera

Hi ,

I could able to solve this.Problem was there with antivirus.Many thanks for all your exceptional support.






Re: How do set send mail task?

Andy Leonard

Hi Savera,

Good job! Way to hang in there.

Andy