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.

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.

Send One Email Instead of Multiple Emails

Accepted answer

ZoeHui-MSFT 33,296 Reputation points

2022-08-29T07:39:43.853+00:00
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.
Please sign in to rate this answer.
jn93 651 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 651 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 33,296 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.
Sign in to comment

Send One Email Instead of Multiple Emails

0 additional answers