我正在嘗試查詢資料庫并發送電子郵件正文中的內容。我嘗試在
運行包時，錯誤是
錯誤：腳本任務中的 0xC001405C：嘗試鎖定變數“User::EmailData”以進行讀/寫訪問時檢測到死鎖。嘗試 16 次后無法獲取鎖。鎖超時。`
錯誤：腳本任務中的 0xC001405D：嘗試鎖定變數“System::InteractiveMode”進行讀訪問和變數“User::EmailData”進行讀/寫訪問時檢測到死鎖。嘗試 16 次后無法獲取鎖。鎖超時。
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 = ?
uj5u.com熱心網友回復：
有一個更好的方法。
腳步：
- 創建將通過 XML/XQuery 為電子郵件生成 (X)HTML 正文的 SQL Server 存盤程序。
- 通過SSIS 執行 SQL任務呼叫該 sp并將其輸出分配給 SSIS 變數。
- 呼叫SSIS 腳本任務，傳遞步驟 #2 中包含 (X)HTML 電子郵件正文的變數以發送電子郵件。
好處：
- 沒有字串連接。
- 不用擔心 NULL 值。
- 非常容易創建，非常容易維護。
- UI 樣式是通過 CSS 控制的。
這是一個概念性的例子。
查詢陳述句
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));
uj5u.com熱心網友回復：
使用你得到的東西，錯誤是從你與變數互動的方式中蔓延出來的。您提供的代碼是他在 2005 時代必須做的事情，盡管那時它已經是 VB.NET，因為 C# 不是一個選項。您應該能夠通過 Dts.Variables 集合直接訪問變數，然后Value根據需要將它們- 轉換為字串或保留為物件型別。
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);
看起來您實際上并未寫入值，因此我將在第一個螢屏截圖中將它們從 ReadWrite 更改為 ReadOnly。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/371008.html
標籤：c# sql-server ssis etl