Hi All,

I have one scenario. I am calling all columns result set to an variable and inside for each loop container using script task to get message about how many columns are coming in the loop.

At last using send mail task to send automated mails to group of people,but issue it is taking only person's mail id and coming out of loop.

Can any one suggest how to call object type variable ?

Regards,

Ven

Hi Ven,

If I understand correctly, you want to make use of the multiple rows returned by Execute SQL Task in Send Mail Task.

As you know, the Full result set is used when the query returns multiple rows with Execute SQL Task, it must map to a variable of the Object data type, then the return result is a rowset object, so we cannot directly use the result variable in Send Mail Task.

To achieve this requirement, we can use a Foreach Loop container to extract the table row values that are stored in the Object variable into package variables, then use a Script Task to write the data stored in packages variables to a variable, and then set the variable as ToLine or MessageSource in the Send Mail Task. For more details, please refer to the following FAQ:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/60baba9d-6dab-43c2-8738-a80c448c3b3c/forum-faq-how-do-i-send-multiple-rows-returned-by-execute-sql-task-as-email-content-in-sql-server?forum=sqlintegrationservices

Thanks,
Katherine Xiong

What does this bit mean "using script task to get message about how many columns are coming in the loop."?

It sounds like you have the general Object variable handling process, are you attempting to access this variable within the script task?

Hi Ven,

You can use Object Variable inside the script task, and you can read that Object variable and loop in the data and write the mail sending code inside the script task and send the mail.

To add the list of users to send the mail, what you can do is to append the mail id to string with Comma(,) delimited or semi colon (;) delimited string and then send to group of users.

Check these links which may help you in reading object variable.

http://beyondrelational.com/modules/2/blogs/106/posts/11133/ssis-reading-object-variable-in-script-task.aspx

https://msdn.microsoft.com/en-in/library/ms403365(v=sql.105).aspx

Regards

Naveen