CODEWITHSUNDEEP
emailvariablesssistasksend
Steve
Steve

Reputation: 11

SSIS Send email Task With Variables

I was asked to send an email to my employees with the following info that I have:

Excel File (EmployeeInfo):

EmpID   EmpName Rank    PromoGift
1       Peter       5       1
2       Armand      4       2
3       Tommy       5       5
4       Sarah       2       4
5       Maria       3       3

CSV File (PromoCode):

PromoID Validation
1       1
2       0
3       1
4       1
5       0

Text File (PromoInfo)

PromoID Gift
1       100$ Apple GC
2       80$ Apple GC
3       60$ Apple GC      
4       40$ Apple GC    
5       20$ Apple GC

Requirements:

1. Only employees that has Validation = 1 will receive a gift.
2. The gift is based on the PromoID (PromoGift)
3. "Data Flow Task, Execute Sql Task, ForEach Loop & Send Mail" Task can only be used to execute the task above.
4. In the email, I need to replace the [...] accordingly.
5. Employee's email are neglect in this exercise.

I will send an email to them with the following message:

Dear [EmpName],

This [CurrentYear], during our [EventName], you have been selected to be the winner for reaching your next level Rank [Rank]. You will receive a [Gift]!

A gift from your Manager [Manager]!

Congratulation!

What should I do (I am new to SSIS)?

Create 3 Data Flow Task and load each files into SQL Server Table. Then Use SQL Task to do a Join, so I can get the list of who will receive the gift, then what's next? And what should I use to insert those variables into my email template? I believe I need to creates variables too.

Any thoughts?

Upvotes: 0

Views: 2513

Answers (1)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Check This.

1.First load each files into SQL Server Tables EmployeeInfo , PromoCode , PromoInfo

2.Open SQL Server and You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings

  1. Query to send mail 

        USE msdb
    GO
    EXEC sp_send_dbmail @profile_name='yourprofilename',
    @recipients='[email protected]',
    @subject='Test message',
    @body='This is the body of the test message.
    Congrates Database Mail Received By you Successfully.'

4.

DECLARE @email_id NVARCHAR(450)='1', @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [EmployeeInfo ]



WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id ,@Gift =gift
    FROM EmployeeInfo e join PromoInfo p on p.id = e.id
    where ID=  @id

    SET @body =  N''+@email_id+'Your Gift Is' +@Gift;

    SET @body = @body 
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile_Name',
    @body = @body,
    @body_format ='HTML',
    @recipients = @email_id,
    @copy_recipients = '',
    @subject = 'Subject' ;

    SELECT @id=MIN(id) FROM [EmployeeInfo ] where id>@id

END

You can use these example and also create SP for that. and call this SP in Your SSIS package.

Upvotes: 1

Related Questions