SSIS Script Task is throwing deadlock error

Question

I am trying to query the database and send the content in the body of the email. I tried following here for my use case. But 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

namespace ST_ac39a1a4cb6047819cc46d683db46ac6
{
    [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("loadJob@xyz.com", User_Recepient_Email_ID, "ETL Load Status Report", ConvertDataTableToHTML(dt), true, "smtp.xxxxxxxx.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);
        }   
    }
}

And the Task Script property

When running the package the errors are

Error: 0xC001405C at Script Task: A deadlock was detected while trying to lock variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.`

Error: 0xC001405D at Script Task: A deadlock was detected while trying to lock variables "System::InteractiveMode" for read access and variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

Error: 0x1 at Script Task: A deadlock was detected while trying to lock variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out. 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 (5) 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 (5) 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

EDIT

Below is the query

SELECT cus.CustomerNumber as CustomerNumber,cus.Location as ReceivingLocation, 
       i.StrainName as StrainName,i.StrainCode as StrainCode,i.Age as Age,
       i.Sex as Sex,i.Genotype as Genotype,i.RoomNumber as SentFrom,io.OrderQuantity as OrderQuantity
FROM [dbo].[MouseOrder] mo
JOIN [dbo].[Customer] cus on cus.Customer_ID = mo.CustomerId
JOIN [dbo].[InventoryOrder] io on io.OrderId = mo.MouseOrder_ID
JOIN [dbo].[Inventory] i on i.Inventory_ID =  io.InventoryId 
WHERE mo.OrderDate = convert(date,getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') and mo.SAMAccountEmail = ?

Is your underlying database SQL Server? – Yitzhak Khabinsky Dec 01 '21 at 16:26 — Yitzhak Khabinsky, Dec 01 '21 at 16:26
Yes it is Azure SQL Database – user4912134 Dec 01 '21 at 16:28 — user4912134, Dec 01 '21 at 16:28

Yitzhak Khabinsky · Answer 1 · 2021-12-01T17:52:33.163

2

There is a better approach.

Steps:

Create SQL Server stored procedure that will generate (X)HTML body for the email via XML/XQuery.
Call that sp via SSIS Execute SQL Task and assign its output to an SSIS variable.
Call SSIS Script Task passing a variable from the step #2 that contains (X)HTML email body to send an email.

Benefits:

No strings concatenation.
No worries for NULL values.
Very easy to create, very easy to maintain.
UI styling is controlled via CSS.

Here is a conceptual example for you.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(20), Number CHAR(5), [Address] VARCHAR(100));
INSERT INTO @tbl (Name, Number, Address) VALUES
('Bob  ', '12345' ,'1 Street, Town'),
('John ', '23456' , NULL),
('Scott', '34567' ,'3 Avenue, City');

DECLARE @xhtmlBody XML
   , @body NVARCHAR(MAX)
   , @tableCaption VARCHAR(30) = 'Customers list';

SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl 
FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            #green <![CDATA[ {background-color: lightgreen;} ]]>
         </style>
         </head>
         <body>
<table border="1">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>No.</th>
        <th>Name</th>
        <th>Number</th>
        <th>Address</th>
      </tr>
   </thead>
   <tbody>
{
    for $row in /root/row
    return <tr>
            <td>{data($row/ID)}</td>
            <td>{data($row/Name)}</td>
            <td>{data($row/Number)}</td>
            <td>{data($row/Address)}</td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

SET @body = CAST(@xhtmlBody AS NVARCHAR(MAX));

edited Dec 01 '21 at 17:52

answered Dec 01 '21 at 17:17

Yitzhak Khabinsky

18,471
2
15
21

Thank you !! I am very new to the SSIS and SP. There is nothing I could fix in the approach I am taking – user4912134 Dec 01 '21 at 17:19
Just copy the proposed solution to SSMS, run it as-is, and see what it does. – Yitzhak Khabinsky Dec 01 '21 at 17:26
Query that generates the email body that has filter with parameter.. Can the SP accept the parameters? – user4912134 Dec 01 '21 at 17:29
Absolutely, YES. – Yitzhak Khabinsky Dec 01 '21 at 17:30
I added the SqL query which has joins and the filter parameter can you please take a look and let me know if I can do that through SP – user4912134 Dec 01 '21 at 17:39
The conceptual example contains `...SELECT * FROM @tbl...`. You just need to replace that line with your `SELECT ...`. – Yitzhak Khabinsky Dec 01 '21 at 17:50
@user4912134, what's the latest on your end? – Yitzhak Khabinsky Dec 01 '21 at 19:35
I tried the SP and running to issue https://stackoverflow.com/questions/70190278/stored-procedure-to-generate-html-response-doesnot-work can you please suggest if I am missing something – user4912134 Dec 01 '21 at 19:38
Impossible to help without an interactive session with the shared screen. – Yitzhak Khabinsky Dec 01 '21 at 19:40
I am sorry I tried as you suggested, but I am not familiar in this area – user4912134 Dec 01 '21 at 19:41
Is the stored procedure is wrong? – user4912134 Dec 01 '21 at 19:41
Please connect with me on LinkedIn. – Yitzhak Khabinsky Dec 01 '21 at 20:00

score 2 · Accepted Answer · answered Dec 01 '21 at 19:54

Working with what you got, the error is creeping in from the way you're interacting with Variables. The code you supplied is how he had to do things back in the 2005 era, although it'd have been VB.NET at that point as C# wasn't an option. You ought to be able to directly access the variables through the Dts.Variables collection and then get their Value - casting to string or leaving as object type as needed.

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

        OleDbDataAdapter da = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        da.Fill(dt, data);

It doesn't look like you're actually writing to the values so I would change them from ReadWrite to ReadOnly in the first screenshot.

Thank you so much it did help resolve the error. But I am not seeing the data in my email. I have posted a different question https://stackoverflow.com/questions/70193123/ssis-script-task-to-send-email-doesnot-show-the-data-correctly can you please help me suggest what is that I am missing — user4912134, Dec 02 '21 at 01:12

SSIS Script Task is throwing deadlock error

2 Answers2

Linked