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.
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)
If the answer is helpful, please click "Accept Answer" and upvote it.
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.
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
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.