-1

i have designed a SSIS Package which will load some data to table from the file available in a folder. once data got loaded file move to archieve.

Client requested to have mail trigger at the end of process, so i implement it through send mail task.

The pooling mechanism was done via agent job which run after evry 1 hour to check existence of file.

The problem starts here.

it start sending mail every 1 hr to user

will it be possible to do some modification.

Thanks You All...... i think my question was not clear. The Problem i am facing is my package is start trigger mail every 1 hour, even when there is no file in source folder. i want to trigger mail when there is a file in Source folder, and after processing the file mail should get triggered, so user can come to know that file which they placed in the folder is got processed. But in present scenario, because i used agent job, which is checking for the file every 1 hour, even if file is not there mail is getting triggered to user. Please help me on this.

Can any one please suggest how can i rectify it.

Thanks

akhrot
  • 415
  • 2
  • 7
  • 18

2 Answers2

0

Yes, there are numerous ways to handle this depending on what you want to do.

They all involve only sending the email if some condition is true. You will have to decide what that condition is, and then that will suggest the best way to handle it.

You can set a variable and use an expression in the precedence constraint to only go to the Send Mail Task if the variable is true, for example.

If you want to look at a more complicated set of conditions, you can use a script task.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

I would have sent mail only if the package fails. Not sure, your purpose of sending mail to the Client.

If the issue is sending mail every 1 hour is too much for the client. Send mail if package fails and keep the status of every hour in a file (Using Script task, use variable to get the current Hour and write the Hour and Status to file) and send a consolidated mail at the end like.

Date: 09/23/2016

Hr - STATUS
-------------
00 - SUCCESS
01 - FAILED
.
.
.
23 - SUCCESS

If you want to send the consolidated mail at particular time, use the Variable used in precedence constraint before the send mail task.

Edit:

As per your update on clarification. Create a boolean variable to get the existence of the file using script task. Use that variable in precedence constraint before the Send Mail task.

http://sql-articles.com/articles/bi/file-exists-check-in-ssis/

Also, Refer

p2k
  • 2,126
  • 4
  • 23
  • 39
  • why store this in a file? Database would be much better place to store the logging. Plus if it is run via SQL Agent you can get that information from the sql agent job log – Matt Sep 23 '16 at 18:13
  • Database is of course better option if allowed to create tables there. – p2k Sep 23 '16 at 18:34
  • Thanks You All...... i think my question was not clear. The Problem i am facing is my package is start trigger mail every 1 hour, even when there is no file in source folder. i want to trigger mail when there is a file in Source folder, and after processing the file mail should get triggered, so user can come to know that file which they placed in the folder is got processed. But in present scenario, because i used agent job, which is checking for the file every 1 hour, even if file is not there mail is getting triggered to user. Please help me on this. – akhrot Sep 25 '16 at 16:12