Send One Email Instead of Multiple Emails

Question

Send One Email Instead of Multiple Emails

jn93 551

Hi All,

Let say I have the ssis package which extract the data from csv file and export to DB. I have created script task to check if the file is the exist. If file not exist, it will flow to send email which the expression shown in the image below . I'm using variable for file name and folder name. What if I have multiple ssis package which the same control flow like above. I will get multiple email, if got many file is not exist when job schedule complete for all the package. Is it possible to consolidate all the message info in one email? like fine tuning the ssis package control flow. Currently, I'm not sure how to make it possible since I got many ssis package.

Answer 1

ZoeHui-MSFT 20,176

Hi @jn93 ,

From my opinion, you may first load the file names to table like shown here.

And then use sql command to compare the two tables to find the files not exist in the folder and then store the result to the variable-filename, after that you may send mail with the filename.

execute-sql-tasks-in-ssis-output-parameters-vs-result-sets

A sample code like below you may edit as you need.

select string_agg(filename,',')  
from table1  
where name not in (select filename from table2)

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

jn93 551 Reputation points

2022-09-02T02:09:42.487+00:00

Hi @ZoeHui-MSFT

Looks like string_agg function is not supporter in my sql. However, I have tried tried below code to concatenate the result and its work fine. But, I do have problem to store the result to the variable-filename. I'm getting the error invalid number of result bindings returned for the ResultSetType:"ResultSetType_SingleRow. I do change the datatype for variable filename to Object, able to execute but there seems to be error when drop the variable inside the send mail task expression. Appreciate your help on this.

Code:
Select SUBSTRING(
(
SELECT ',' + a. FILENAME AS 'data()'
FROM [ef].[temp_TABLES] a LEFT JOIN [dbo].[FE_LOADLOG] b ON a.FILENAME=b.FILENAME WHERE b.FILENAME IS NULL
FOR XML PATH('')
), 2 , 9999) As FILENAME
jn93 551 Reputation points

2022-09-02T02:13:33.983+00:00

Hi @ZoeHui-MSFT

Looks like string_agg function is not supporter in my sql. However, I have tried tried below code to concatenate the result and its work fine. But, I do have problem to store the result to the variable-filename. I'm getting the error invalid number of result bindings returned for the ResultSetType:"ResultSetType_SingleRow. I do change the datatype for variable filename to Object, able to execute but there seems to be error when drop the variable inside the send mail task expression. Appreciate your help on this.

Code:
Select SUBSTRING(
(
SELECT ',' + a. FILENAME AS 'data()'
FROM [ef].[temp_TABLES] a LEFT JOIN [dbo].[FE_LOADLOG] b ON a.FILENAME=b.FILENAME WHERE b.FILENAME IS NULL
FOR XML PATH('')
), 2 , 9999) As FILENAME
ZoeHui-MSFT 20,176 Reputation points

2022-09-02T02:23:39.187+00:00

Hi @jn93 ,
Sorry for my unclear explain, string_agg function could only be used in SQL Server 2017 and later.
I set the datatype for variable filename to string as shown below. a2,a3 is the two file names.

Send One Email Instead of Multiple Emails

0 additional answers

Activity