CODEWITHSUNDEEP
ssis
Vikram D
Vikram D

Reputation: 57

Dynamically attach Files to the email in SSIS

I have a requirement to send invoices as attachment to customers.

enter image description here

As shown in the above screenshot a table in the database contains Name of the customer and attachment name (which contains name). I need to iterate through this table and send attachment to concerned customer. These same attachments are saved in a folder. Please Help me with an approach.

Upvotes: 2

Views: 11068

Answers (3)

Hiran
Hiran

Reputation: 1180

It's easy in SSIS.

  • get the file name to a variable in SSIS
  • open the send mail task then go to expression.
  • then you can set the FileAttachment property to above variable.

Upvotes: 1

VKarthik
VKarthik

Reputation: 1429

As Alleman pointed out, sp_send_dbmail is the best option to go about doing the task. The only permissions that one needs to be assigned is DatabaseMailUser database role in the msdb database.

However if you feel it needs to be addressed via SSIS. Here are the steps to follow -

  1. Create a object variable say objInvoiceAttachmentList and following three variables - sCustomerName, sAttachmentFullFilePath, sMailId

  2. Use 'Execute SQL Task' and run a query to get the list of customers data as below. Set the result set as 'Full Result Set'. In the 'ResultSet' tab assign it to a above object variable. (Am assuming you have a column called MailId for your customers)

    select name,attachment,MailId from dbo.InvoiceAttachments

  3. Use 'For Each Loop Container'. In the 'Collection' set the enumerator as 'Foreach ADO Enumerator' and give the collection variable as objInvoiceAttachmentList. In 'Variable Mappings' tab, assign the three variables in the order of the query.

  4. Use 'Send Mail Task' within the foreach loop task. Go to 'Expressions' and set the appropriate variables with a proper valid SMTP Connection.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

You don't need to use SSIS. In fact, SSIS is really not a very good tool for this.

sp_send_dbmail can send attachments from the file system. Use the @file_attachments parameter.

Upvotes: 0

Related Questions