Send One Email Instead of Multiple Emails

jn93 651 Reputation points
2022-08-26T08:26:03.577+00:00

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.

235201-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,856 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 33,386 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.


0 additional answers

Sort by: Most helpful