July 9, 2020 at 7:37 pm
I am tryting to convert the below procedure into SSIS, even if i get temp1 and temp2 into object variable.. how can i use those in for each loop container where it starts while condition in ssis?
BEGIN TRY
BEGIN
Select
PG.ID
, PG.Time
,G.Address
,PG.Date
,b.Description as [D]
,b.Rec
INTO #temp1
FROM tblPep PG
INNER JOIN tblRe RSV ON rsv.ID = PG.ID
INNER JOIN tblDg G ON G.ID = RSV.ID
inner join tblXsb on b.id = G.ID
WHERE
G.ID = -14
SELECT
Address
, ROW_NUMBER() OVER(ORDER BY [Address]) AS [Cnt]
INTO #temp2
FROM #temp1
GROUP BY Address
DECLARE @html VARCHAR(8000)
DECLARE @Rec VARCHAR(100)
DECLARE @r INT
SET @r = 1
WHILE @r <= (SELECT COUNT(*) FROM #temp2)
BEGIN
SET @Rec = (SELECT [Address] FROM #temp2 WHERE [Cnt] = @r)
SELECT @html = '<html><body style="font-family: ''EYInterstate Light'',Arial,sans-serif;">
your id selected
'
SELECT @html = @html + '
<table border="1"><tr><th>Time</th><th>ID</th><th>Desc</th><th>Sl</th></tr>'
SELECT
@html =
@html +
('<tr><td width="200px;">' + CONVERT(VARCHAR, Time,0 )
+ '</td><td width="150px;"> ' + Cast(ID as varchar(15)) + '</td>' +
+ '</td><td width="250px;"> ' + [D] + '</td>' +
'<td width="150px;"> ' + Rec+ '</td></tr>')
FROM #temp1
WHERE [Address] = @Rec
SELECT @html = @html + '</table>
<br/>'
Select @html = @html + '----- https://covid.ey.net/ -----.'
SELECT @html = @html + '
</html>'
DECLARE @Sub VARCHAR(100)
SET @Sub = '-------'
EXEC msdb.dbo.sp_send_dbmail
@recipients = @Rec
, @subject = @Sub
, @body_format = 'html'
, @body = @html
END
END
END TRY
BEGIN CATCH
BEGIN
Declare @err nvarchar(1000)
Select @err = ERROR_MESSAGE()
EXEC msdb.dbo.sp_send_dbmail
, @subject = 'Error'
, @body_format = 'html'
, @body = @err
END
END CATCH
July 10, 2020 at 1:41 am
I am curious why you want to convert the procedure to an SSIS package? Is the procedure not working as needed?
July 10, 2020 at 3:35 pm
We are getridding of EXEC msdb.dbo.sp_send_dbmail.. to SMTP send mail task..
July 10, 2020 at 3:39 pm
Your procedure contains at least 1 syntax error. Why do you refuse to format your code?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 10, 2020 at 4:11 pm
USE [OTandE_Dev]
GO
/****** Object: Trigger [heartbeat].[HeartBeat_SendEmail] Script Date: 7/10/2020 10:00:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [heartbeat].[HeartBeat_SendEmail]
ON [heartbeat].[HeartBeat]
AFTER INSERT
AS
BEGIN
/**********************************************************************************************************************************************************************************************
Trigger: [heartbeat].[HeartBeat_SendEmail]
Author: Lynn A. Pettis
Date: 2019-05-16
Site: Polaris Alpha, A Parsons Company - Colorado Springs, CO
This trigger is used to send HeartBeat emails to appropriate people regarding the status of monitored servers.
An email will be sent on the status of a new server build and then on subsequent changes in status of a server or
any of the installed applications on the server.
Initally, only two emails are being generated and sent, one for system administrators and a second for project managers.
Change Log:
Date Developer JIRA Version Notes
========== ==================== ======== ======= =========================================================================================================================================
2019-06-12 Lynn Pettis SI-544 1.1 Refactor trigger deleting program manager email.
Modify trigger to use the email address inserted into the HeartBeat table.
Comment reply to line. This was used to verify receipt of email during testing.
**********************************************************************************************************************************************************************************************/
DECLARE
@AppName varchar(64)
, @EmailNotification varchar(MAX)
, @tableHTML1 nvarchar(MAX)
, @tableHTMLFormat nvarchar(MAX)
= N'Server Status Report
<table border= "1">
<tr>
<th>Server (fqdn)</th>
<th>Test Date and Time</th>
<th>Application Name</th>
<th>Application Test Date and Time</th>
<th>Test Result</th>
<th>Application Version</th>
</tr>'; -- HTML header fragment used to generate the HTML message body for the HeartBeat Email notification.
-- The rest of the HTML is generated in the following query.
SET NOCOUNT ON;
SELECT
@AppName = [appName]
, @EmailNotification = [EmailNotification]
FROM
[inserted];
IF (@AppName = 'TestResult')
BEGIN
SET NOCOUNT ON;
WITH [BaseDate] AS (
SELECT
[hb].[fqdn]
, [batchDate] = CONVERT(
varchar(30)
, CAST(TODATETIMEOFFSET([hb].[batchDate], 0)AT TIME ZONE 'Mountain Standard Time' AS datetime)
, 120
)
, [appName] = CASE
WHEN [hb].[appName] = 'TestResult'
THEN
'Server Status'
ELSE
[hb].[appName]
END
, [testDate] = CONVERT(
varchar(30)
, CAST(TODATETIMEOFFSET([hb].[testDate], 0)AT TIME ZONE 'Mountain Standard Time' AS datetime)
, 120
)
, [testResult] = CASE
WHEN [hb].[testResult] = 0
THEN
'Down'
WHEN [hb].[testResult] = 1
THEN
'Up'
END
, [hb].[appVersion]
, [hb].[EmailNotification]
, [previousTestResult] = [oa1].[testResult]
, [previousServerCreationDateTime] = [oa1].[ServerCreationDateTime]
FROM
[heartbeat].[HeartBeat] AS [hb]
INNER JOIN [inserted] AS [ins]
ON [hb].[fqdn] = [ins].[fqdn] AND [hb].[batchDate] = [ins].[batchDate]
OUTER APPLY (
SELECT
[hb2].[testResult]
, [hb2].[ServerCreationDateTime]
FROM
[heartbeat].[HeartBeat] AS [hb2]
WHERE
[hb2].[appName] = [hb].[appName]
AND [hb2].[batchDate] = (
SELECT
MAX([hb3].[batchDate])
FROM
[heartbeat].[HeartBeat] AS [hb3]
WHERE
[hb3].[batchDate] < [hb].[batchDate]
AND [hb3].[fqdn] = [hb].[fqdn]
AND [hb3].[ServerCreationDateTime] = [hb].[ServerCreationDateTime]
)
) AS [oa1]
WHERE
[hb].[testResult] ^ ISNULL([oa1].[testResult], CAST(~ CAST([hb].[testResult] AS bit) AS int)) = 1 -- If Current testResult and previous testResult are the same, do not return the row (XOR condition)
-- If oa1.testResult is null, use the inverse value of the current testResult to trigger an email.
)
SELECT
@tableHTML1 = @tableHTMLFormat + CAST((
SELECT
= [bd].[fqdn]
, ''
,= [bd].[batchDate]
, ''
,= [bd].[appName]
, ''
,= [bd].[testDate]
, ''
,= [bd].[testResult]
, ''
,= [bd].[appVersion]
FROM
[BaseDate] AS [bd]
ORDER BY
[bd].[testDate]
FOR XML PATH('tr'), TYPE
) AS nvarchar(MAX)) + N'</table>';
IF @tableHTML1 IS NOT NULL
EXEC [msdb].[dbo].[sp_send_dbmail]
@recipients = @EmailNotification -- 'SI-OTandE@acompany.com'
--, @reply_to = 'testreply@acompany.com'
, @subject = 'System Admin - Server Status Report'
, @body = @tableHTML1
, @body_format = 'HTML';
END;
END; -- TRIGGER [dbo].[HeartBeat_SendEmail]
GO
ALTER TABLE [heartbeat].[HeartBeat] ENABLE TRIGGER [HeartBeat_SendEmail]
GO
This is the code that comprises the trigger.
July 10, 2020 at 4:51 pm
We are getridding of EXEC msdb.dbo.sp_send_dbmail.. to SMTP send mail task..
Okay - no real reason to do this since it works from SQL Server, but to do this you are going to have to change your approach.
For something like this...I wouldn't use SSIS. This really seems more like an SSRS solution with a data-driven subscription - a simple report with a parameter to filter based on your criteria and a simple query to return the list of address information (you can include TO, CC, BCC, SUBJECT, etc...). The subscription could then be setup to send as MHTML so the report is embedded in the email.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply