CODEWITHSUNDEEP
ssis
BrajeshKr
BrajeshKr

Reputation: 11

Dynamic mailing in SSIS

Is there any way in SSIS to send mail dynamically with dynamic subject, dynamic recipients, dynamic body itself? It is possible with database tables but I dont want to use database tables and stored procedures in my package.

Upvotes: 1

Views: 706

Answers (2)

billinkc
billinkc

Reputation: 61211

SSIS is an in-memory ETL (Extract, Transform and Load) tool that is powered by the .NET framework (and some COM hackery). It does have a Send Mail Task which is capable of sending emails.

To make them as dynamic as you describe, you'll want to use a combination of Expressions, as Aryadev linked, coupled with Variables.

Most every thing in SSIS exposes a set of Properties that support configuration so what I generally suggest is that people create variables and perform all their expressions on the variables and then assign a variable to a property's expression. While it seems like more work, when you have to troubleshoot a package you'll be grateful. You can set a breakpoint in a package and inspect the current value of a Variable but you can't examine the run-time value of a property.

Finally, unless you're doing ETL stuff in addition to email, I'd skip the overhead of SSIS in favor of just using sp_send_dbmail in a TSQL space or call into the .NET smtp library.

Upvotes: 2

Alexey A.
Alexey A.

Reputation: 902

Yes. Look into SSIS Expressions.

Upvotes: 0

Related Questions