Dynamic Subject Line

Question

Post reply

Dynamic Subject Line

etvph81

SSC Veteran

Points: 234
More actions
March 4, 2020 at 7:53 pm

#3731040

Hello All,
I created a SSIS Package that will send a report weekly is there a way to write a script where each time report is sent it will put in the subject line "Example Transfer Report for the week of xx-xx-xxxx" every week it sends it will up date the xx-xx-xxxx every week.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

When I send e-mails from SSIS, I usually use an ExecuteSQL task which runs sp_send_dbmail (link).

If you configure this, you have all the control you should ever need.

If you are using the native SSIS SendMail task, all you need to do is create an Expression for the Subject. Something like this:

"Example Transfer Report for the week of " + (DT_STR, 2, 1252) day(getdate()) + "-" + (DT_STR, 2, 1252) month(getdate()) + "-" + (DT_STR, 4, 1252) year(getdate())

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

2020-03-04_15-06-42

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

etvph81 SSC Veteran Points: 234 More actions · Answer 3

Here is my current Execute SQL Task :

DECLARE @FilePath nvarchar(max) = 'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\'

DECLARE @Filenames nvarchar(max)

DECLARE @EmailAddress varchar(max)

SET @EmailAddress = '?'

SET @Filenames = @FilePath + 'dc_weekly' + '.csv'

EXEC msdb.dbo.sp_send_dbmail

@Recipients = WESA,

@Subject = N'DC Transfer Files',

@Body = N'Attached please find Transfer Report.',

@Body_format = 'HTML',

@File_attachments = @Filenames;

SET @EmailAddress = 'edward.vahovick@gimi.org'

EXEC msdb.dbo.sp_send_dbmail

@Recipients = @EmailAddress,

@Subject = 'Transfer Report',

@Body = 'Attached please find user login reports.',

@Body_format = 'HTML',

@File_attachments = @Filenames;

where in here would I put this code at I apoligize I'm new to this language

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Modify the line

@Subject = 'Transfer Report',

to

@Subject = CONCAT('Transfer Report for the week of ', CONVERT(CHAR(10), GETDATE(), 101)),

and see whether that is getting you close to what you need.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

etvph81 SSC Veteran Points: 234 More actions · Answer 5

Its not liking youre @Subject = CONCAT('Transfer Report for the week of ', CONVERT(CHAR(10), GETDATE(), 101)),

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Transfer Report for the week of '.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

My apologies. This is better:

DECLARE @FilePath NVARCHAR(MAX) = N'C:\inetpub\ftpuser\WESA\ftpwashingtondc\DCWeeklyEmail\';
DECLARE @Filenames NVARCHAR(MAX);
DECLARE @EmailAddress VARCHAR(MAX);

SET @EmailAddress = '?';
SET @Filenames = @FilePath + N'dc_weekly' + N'.csv';

DECLARE @Sub NVARCHAR(255) = CONCAT('Example Transfer Report for the week of ', CONVERT(CHAR(10), GETDATE(), 101));

EXEC msdb.dbo.sp_send_dbmail @recipients = WESA
                            ,@subject = @Sub
                            ,@body = N'Attached please find Transfer Report.'
                            ,@body_format = 'HTML'
                            ,@file_attachments = @Filenames;

SET @EmailAddress = 'edward.vahovick@gimi.org';

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress
                            ,@subject = @Sub
                            ,@body = 'Attached please find user login reports.'
                            ,@body_format = 'HTML'
                            ,@file_attachments = @Filenames;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

etvph81 SSC Veteran Points: 234 More actions · Answer 7

Thanks Phil I appreciate it how would I get it to say for the week of 23 to 29th?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

etvph81 wrote:

Thanks Phil I appreciate it how would I get it to say for the week of 23 to 29th?

Please define the logic for determining the numbers to use.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

etvph81 SSC Veteran Points: 234 More actions · Answer 9

My CSV file that i'm using right now contain the dates f dcweek or the week of 23 to 29th but the subject line that I used that code for ie showing

etvph81 SSC Veteran Points: 234 More actions · Answer 10

Subject line needs to state previous week if possible.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

So ... if the job ran today, would you like the subject to be

'Transfer Report for the week of 23 March to 29 March'

?

This reply was modified 4 years, 8 months ago by Phil Parkin.
This reply was modified 4 years, 8 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

etvph81 SSC Veteran Points: 234 More actions · Answer 12

Actually for the week of February 23rd

again I really appreciate the help

etvph81 SSC Veteran Points: 234 More actions · Answer 13

I wasn't sure if running this package weekly if that function would update automatically every week

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

etvph81 wrote:

Actually for the week of February 23rd
again I really appreciate the help

Oops, I meant Feb.

Change your DECLARE @Sub line as follows:

DECLARE @Sub NVARCHAR(255)
    = CONCAT(
                'Transfer Report for the week of '
               ,FORMAT(DATEADD(DAY, -1, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0)), 'MMMM dd')
            );

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin