May 11, 2012 at 9:49 am
So I have my package which basically pulls csv files from an ftp site to a local folder and then my task loops through the files and imports them into a table, once this has finished I then delete all files locally and from the ftp site.
The package runs every hour, and I would like to send an e-mail upon completion of the job, but I only want the email to go out if the package actually imported any data, I don’t want e-mails being sent at the end of the job if there were no files processed.
Any ideas on how I can achieve this please.
May 11, 2012 at 10:52 am
Your Foreach loop maps file names into a variable. A precedence constraint after your Foreach loop connecting it to a Send Email Task could check to see whether that variable has a non-empty string as its value. If it has a non-empty value you'll know at least one file was processed and it should carry on to send an email.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 1:37 pm
I generally have an Execute SQL task that goes to my staging table to be sure there are at least a certain number of records. Evaluating @@RowCount gives you that ability to compare against threshold.
May 11, 2012 at 11:42 pm
If you want to be a bit more sophisticated, one thing you could do would be to set up an import-logging table along the lines of:
BatchNo
FileName
RecordsInFile
RecordsImported
RecordsUpdated
Your package would need to generate a unique batch number every time it runs.
Then, for every file processed, a new row is inserted accordingly.
Then, at the end of processing, your e-mail can be conditional on Sum(RecordsInFile) > 0 where BatchNo = nnn. If true, you can include lots of useful info in the e-mail too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 18, 2012 at 8:59 am
Hi All,
Can anyone of you, explain this with example.
This post is very much good, i would like to jump in this post.
Thanks,
Jay
May 18, 2012 at 9:16 am
rather generic, I don't include a lot here. We have a custom email sp to add formatting in keeping with corp look and feel for such things, but this is the scaled-down simple email. At start of every package, I create a staging and empty table, so these already exist:
if exists (select 1 from sys.objects where object_id = object_id('#tmpTAB1')) drop table #tmpTAB1
CREATE TABLE #tmpTAB1
(
[ClientID] char (10) NOT NULL
) ON [PRIMARY]
insert into #tmpTAB1
SELECT top 10 ClientID
FROM mylivetableStaging (NOLOCK)
If @@RowCount < 10
BEGIN
exec msdb.dbo.sp_send_dbmail
@recipients = 'me@workmail.com,myboss@workmail.com',
@subject = 'SSIS Validation - Package name - Failure - Less than 10 rows in Staging' ,
@body = 'Staging flip prevented due to small population in Staging',
@body_format = 'html',
@profile_name = 'whateveryouuse'
END
ELSE
BEGIN
ALTER TABLE mylivetable switch TO mylivetableEmpty
ALTER TABLE mylivetableStaging switch TO mylivetable
DROP TABLE mylivetableStaging
Drop TABLE mylivetableEmpty
END
drop table #tmpTAB1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply