Hi All,

First of all, I am very new to SSIS. I came across a requirement that I need create files and send emails, I have done experiments on these two individually. But together this is what I need to do and create a package that does all..



 

From the result of a SQL query above, in a location create excel files Administration.xlsx, Cafeteria.xlsx, Front Office.xlsx that has their own data rows, then email those files to the appropriate employee. Person1 and person2 will receive Administration.xlsx, manager will receive Cafeteria.Xlsx and sec will receive Front Office.xlsx.

Is it possible to create a dtsx package that does them all? Pls advise and Thank you in advance.

Ram

Sure is.

You're going to want to create an object variable, and then an Execute SQL Task, set it's resultSet to Full Result Set, and then head to the Result Set tab, and put result name 0 into your variable.

You can now use an ADO enumerated For Each loop to loop over the rows.

Yep, in addition to Patrick's reply

Hi Hanu Ram,

here are some links those might help 

Read EmailTo,EmailFrom,Subject and Body From Text File and Send Emails by using Send Mail Task in SSIS Package

Read Subject,Reciptient and Body from SQL Server Table and Send Email by Using Send Mail Task

Create a file with Date From SQL Server Table and Send as Attachment in SSIS Package

Thank You, 

I solved half the problem, however i am having difficulty in using the send mail task. They execute but the send mail task fails and doesn't send mails. I want to use outlook to send mails. I am not sure what I am missing  

Can you provide the fail message?

You need to set up your mail server with the right credential. Anyway, does the system give you any error in return?

No errors were thrown, but I don't receive any email with this. This is what I just had in the package. I tried the experiment with one send mail task and it didn't work

I wonder if the smtp server not properly set up or something. Any suggestions?

Thank you!

 

The Fact it's red tells you there was an error. Look at the execution log.

Hi Ram,

Just as Patrick said, a red color indicates that the component failed.

To troubleshoot this issue, could you post the error messages from the Output Windows pane or Execution Results pane? It seems that there should be some error messages for the Send Mail Task in the Output pane. For example, [Send Mail Task] Error: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender".

Thanks,
Katherine Xiong