asked on

SSIS - Send Mail Task - Multiple attachments

I have created an SSIS package that created 4 .csv files and stores them within my required location.
The files exported have an expression that allocates today's date so that each file is saved and renamed on a daily basis.

My Expression to create the File name for the various files looks like this; ( This is part of the Flat File Connection String)

"\\\\Lonbilling\\Billing\\Fraud Alert\\CLI_Spend_" + (DT_WSTR,4)DATEPART("dd",GETDATE()) + (DT_WSTR,4)DATEPART("mm",GETDATE()) +
(DT_WSTR,4)DATEPART("yy",GETDATE()) +".csv"

The Evaluated Value looks like this;
\\Lonbilling\Billing\Fraud Alert\CLI_Spend_22102008.csv

I have 4 files with slightly different names all within the same location I want to pick up and attach within an email based on the current date.

I want to use the Send mail task to email all 4 attachments within one email to my recipients within the same SSIS package.

Please can someone tell me how I can attach multiple files into the Send Mail task based on my expression ? There is a FileAttachment Expression available however I don't know the correct code to specify more than one file ?

Help would be appreciated ?

PedroCGD

Check the post:
http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx

and search for:
myHtmlMessage.Attachments.Add(New Attachment("c:\example.txt"))

use a script task in spite Send Mail task
use VB or C# code to be more flexible.

pass the c:\example.txt as SSIS global variable to the script
helped?
Regards

EHardie

ASKER

Hi,

Thanks but not sure if this will work also my VB skills are non existant.
Basically I need to pick up these 4 files daily based on the naming convention and date.
So each day the mail needs to pick up the correct file e.g.FILE NAME + Current Date Stamp
So tomorrow's files will be e.g.CLI_Spend_23102008.csv
Can I script it to look this up , using the script task ?

ASKER CERTIFIED SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Hogg

Hi,
There is a simple email component you can run SQL Server 2005, so long as it is not 64 bit. XPSMTP. http://sqldev.net/xp/xpsmtp.htm This gets you your email capabilities through an execute SQL Task.
If you need to use the SendMailTask...
Attachments
Provide the file names of attachments to the e-mail message, delimited by the pipe (|) character for multiple files.
Where are you in the process - can I give you some specific help at your current point?

PedroCGD

or you can send email by a webservice as I do in my projects!
regards