I am trying to query the database and send the content in the body of the email. When I try to run the package it throws deadlock error. Can anyone please suggest what is that I am missing

Script is like below

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.Data.OleDb;
using System.Net.Mail;
using System.Net;
#endregion

namespace ST_c074d0acfee7488b96d42a0f858efee7
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }

        public void Main()
        {
            Variables varCollection = null;

            string User_Recepient_Email_ID = Dts.Variables["User::UserEml"].Value.ToString();

            //Dts.VariableDispenser.LockForWrite("User::EmailData");
            //Dts.VariableDispenser.GetVariables(ref varCollection);
            var data = varCollection["User::EmailData"].Value;

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, varCollection["User::EmailData"].Value);

            SendMailMessage("[email protected]", User_Recepient_Email_ID, "ETL Load Status Report", ConvertDataTableToHTML(dt), true, "smtp.xxxxxxxxxxxxx.org");

            Dts.TaskResult = (int)ScriptResults.Success;
        }


        public static string ConvertDataTableToHTML(DataTable dt)
        {
            string html = "<table border ='1'>";
            //add header row
            html  = "<tr>";
            for (int i = 0; i < dt.Columns.Count; i  )
                html  = "<th>"   dt.Columns[i].ColumnName   "</th>";
            html  = "</tr>";
            //add rows
            for (int i = 0; i < dt.Rows.Count; i  )
            {
                html  = "<tr style='color:blue;'>";
                for (int j = 0; j < dt.Columns.Count; j  )
                    html  = "<td>"   dt.Rows[i][j].ToString()   "</td>";
                html  = "</tr>";
            }
            html  = "</table>";
            return html;
        }
        private void SendMailMessage(string From, string SendTo, string Subject, string Body, bool IsBodyHtml, string Server)
        {
            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(From, SendTo, Subject, Body);
            htmlMessage.IsBodyHtml = IsBodyHtml;

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            mySmtpClient.Send(htmlMessage);
        }
    }
}

Below is the

And the package looks like below.. For every user I need to get all the order and order details and send it to them as email. SO I have the script within the Loop

Earlier I was getting deadlock error and I changed few things referring to different articles and now I am getting below error

Error: 0x1 at Script Task: Object reference not set to an instance of an object.
Task failed: Script Task
Warning: 0x80019002 at Foreach Loop each User: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at SurplusMouse_EmailOrderDetail: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package 

CodePudding user response：

My guess is that this is your error (varCollection is null and you are referencing something that is null):

var data = varCollection["User::EmailData"].Value;

Assign it just like you did the line before...

var data = Dts.Variables["User::EmailData"].Value;

However, I think you are trying this. Email Data is a recordset stored in an object. this is the logic you want for that.

using System.Data.OleDb;

DataTable dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::emailData"].Value);

foreach (DataRow row in dt.Rows)
{
   //insert what you want to do here
}