How to send a mail in SSIS only if record exists in the text file which is generated by the stored procedure called inside it

See more:

C#

SQL

.NET

ADO.NET

SSIS

Hi Folks,

I have a stored procedure which generates a .csv file, now if this CSV file contains any record then only SSIS should trigger a mail, I am a beginner in SSIS so don't know how to implement this.

My stored procedure doesn't have any output and input parameter. The condition at end in my SP is

IF (select COUNT(*) FROM Table) > 1
begin 
  exec master..xp_cmdshell @SQL 
  return 1;
  end
  else return 0

As of now there are 2 tasks in my control flow in SSIS
1. Execute SQL task for calling Stored procedure
2. Send mail task for sending mail

Now, I want step 2 to get executed only my SP returns 1 or else it should not execute send mail task. Could anyone please help on this?

What I have tried:

I have used below login in my main method of script task but it's not working properly

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

string returnValue = (string)cmd.ExecuteScalar();
MessageBox.Show(returnValue);
if(returnValue=="1")
Dts.TaskResult = (int)ScriptResults.Success;
else
Dts.TaskResult = (int)ScriptResults.Failure;