using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
 
public void Main()
{
    // Retrieve the variable values
    string zipFileName = Dts.Variables[“User::ZipFileName”].Value.ToString();
    string storedProcResult = string.Empty;
 
    // Connection string using Windows Authentication
    string connectionString = “Data Source=your_server_name;Initial Catalog=your_database_name;Integrated Security=True;”;
 
    // SQL command to execute the stored procedure
    string sqlCommand = “EXEC dbo.spZipFileCounts @ZipFileName”;
 
    // Create and open a connection to the SQL Server
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(sqlCommand, connection);
        command.CommandType = CommandType.Text;
 
        // Add the parameter for the stored procedure
        command.Parameters.Add(new SqlParameter(“@ZipFileName”, SqlDbType.NVarChar, 255));
        command.Parameters[“@ZipFileName”].Value = zipFileName;
 
        try
        {
            connection.Open();
 
            // Execute the command and read the result
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    storedProcResult = reader[0].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            // Handle any errors
            Dts.Events.FireError(0, “Script Task”, ex.Message, string.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
            return;
        }
    }
 
    // Store the result in the SSIS variable
    Dts.Variables[“User::StoredProcResult”].Value = storedProcResult;
 
    // Prepare the email body
    string emailBody = “The result from the stored procedure is:n” + storedProcResult;
    Dts.Variables[“User::EmailBody”].Value = emailBody;
 
    Dts.TaskResult = (int)ScriptResults.Success;
}
using System.Net;
using System.Net.Mail;
 
// Set up the email details
string fromAddress = “your_email@example.com”;
string toAddress = “recipient@example.com”;
string subject = “Result from Stored Procedure”;
string body = Dts.Variables[“User::EmailBody”].Value.ToString();
 
// Set up the SMTP client
SmtpClient smtpClient = new SmtpClient(“your_smtp_server”);
smtpClient.Port = 587; // or the port your SMTP server uses
smtpClient.EnableSsl = true; // Set this to true if your SMTP server requires SSL
smtpClient.Credentials = new NetworkCredential(Environment.UserName, Environment.UserDomainName);
smtpClient.UseDefaultCredentials = true;
 
// Create the email message
MailMessage mailMessage = new MailMessage();
mailMessage.From = new MailAddress(fromAddress);
mailMessage.To.Add(toAddress);
mailMessage.Subject = subject;
mailMessage.Body = body;
mailMessage.IsBodyHtml = true; // Set to true if the body is HTML
 
// Send the email
smtpClient.Send(mailMessage);