September 14, 2012 at 2:01 pm
I have a peculiar problem here.
I have a procedure that needs to be called at the end of each package, which in turn does a few things for house keeping and then sends out an email. This is in an SSIS task for SQL 2008, so I call a procedure passing the name of the package as a parameter. This procedure we hope to use for each task we write.
The exec sp_send_dbmail is set up as below, and I use a variable for the @Subject = @P part of it.
EXEC MSDB.dbo.sp_send_dbmail @profile_name = 'SQL99',
@recipients = @TO,
@copy_recipients = @cc,
@blind_copy_recipients = @BC,
@subject = @P,
@body = @Msg,
@query = @Q,
@query_result_width = 512,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt'
It runs fine, and everything returns green. The problem is is that it never shows up in my mailbox.
If I use @subject = 'test',, the email shows up.
If I do the following, it shows up.
DECLARE @P VARCHAR(1000)
SET @P = 'Package Name: '
with
@subject = @P,
If I do anything with the actual variable I have passed, it does not show up.
DECLARE @P VARCHAR(1000)
SET @P = 'Package Name: ' + CONVERT(NVARCHAR(900),@PackageName)
I stopped sending the variable to the procedure, and tried to determine the package name within the procedure, but it still did not work. Any time I use the package name from any source it does not work. This has my completely confused.
Any suggestions?
September 14, 2012 at 2:05 pm
How is @P getting its value when you aren't getting the email?
September 14, 2012 at 2:09 pm
Under ideal conditions I pass a parameter @PackageName.
If I use @subject = @PackageName, no mail is received
If I use SET @P = @PackageName
and then use @subject = @P, no mail is received
September 14, 2012 at 2:13 pm
a deslandes (9/14/2012)
Under ideal conditions I pass a parameter @PackageName.If I use @subject = @PackageName, no mail is received
If I use SET @P = @PackageName
and then use @subject = @P, no mail is received
And how does the parameter @PackageName get populated?
September 14, 2012 at 2:25 pm
In the SSIS Execute SQL task I have the following:
EXEC SSIS_MGMT.dbo.LogPackageEnd
@PackageLogID=?
, @BatchLogID = ?
, @EndBatchAudit = ?
, @PackageName = ?
And I have set the System::PackageName as the fourth parameter.
September 14, 2012 at 2:34 pm
Okay, so you have no idea if there is ever a value passed down to the procedure, do you.
My guess, untested, is that the value being passed in is null and even though the procedure apparently returns success, nothing is being sent as a result.
I guess I will leave it to you to test this theory.
September 14, 2012 at 9:48 pm
Actually I have tested it. I have saved it into another table and the package name is there. It is also a System variable in the SSIS package itself, so I have some assurance that it knows it's own name.
I agree that it sounds like the value is null, but even when I use ISNULL() on the parameter, either in an IF statement or ISNULL(@P, 'use this') the email is not being received.
Bottom line is if I use the parameter, the email is not being received. All signs show that it is being sent, and other emails without the parameter are being sent and received.
This is why I am frustrated about it! But thanks for listening, some times that can solve things, and thanks for the suggestions.
April 28, 2017 at 1:34 pm
Ok so I have similar problem.
I have a package that reads a file processes it and inserts the data into a table, very simple.
I have all that working, now I want to be notified if the package fails, I can get that to work, however getting the package name in the email, I haven't been able to do, I don't want to hard code the package name, otherwise I'd have one for each package.
I am try to get the system variable passed to the stored procedure. Here's my stored procedure. I have tried to set the Parameter mappings and setting the Result Set but it errors with sp expects param @PKG but was not supplied, any help would be appreciated. It's SQL2012, This is also in the event handler at package level, OnError
CREATE PROCEDURE [dbo].[spSalesDataErrorsV2] @PKG VARCHAR(200)
AS
declare @recipients varchar(255)
declare @subject varchar(255)
EXEC msdb.dbo.sp_send_dbmail @recipients= 'abc@email.com',
@subject = @PKG,----Currently I have the package name hard coded
@body = 'The package failed'
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply