Skip to Content
0
Aug 10, 2016 at 09:12 AM

Status Mail as summery

56 Views

Hi

I have a ESP project to transfer some data from CSV file into a HANA DB.

I try to send a status mail after import a file.

I attached a aggregate with group by and count(*).

Now, it sends by every row a mail.

How can I send a mail only the file import is finished.

Thanks and regards

Maic

Here is my CCL.

-----------------------------------------------------------------------------------------------------------------------

CREATE SCHEMA schemaCSV (

Tabelle string ,

AnzRows long ,

DataUsedKB long ,

IndexUsedKB long ,

Fragmentierung decimal(34,30) ,

ScanTimestamp bigdatetime ,

KID string ,

SID string ,

Hostname string ) ;

CREATE SCHEMA schemaHANA (

Table string ,

AnzRows long ,

DataUsedKB long ,

IndexUsedKB long ,

Fragmentierung decimal(34, 0 ) ,

ScanTimestamp bigdatetime ,

KID string ,

SID string ,

Hostname string ,

CreateAt bigdatetime ) ;

CREATE INPUT STREAM isCSV SCHEMA schemaCSV ;

CREATE LOCAL STREAM Compute1

AS

SELECT

isCSV.Tabelle Tabelle ,

isCSV.AnzRows AnzRows ,

isCSV.DataUsedKB DataUsedKB ,

isCSV.IndexUsedKB IndexUsedKB ,

isCSV.Fragmentierung Fragmentierung ,

isCSV.ScanTimestamp ScanTimestamp ,

isCSV.KID KID ,

isCSV.SID SID ,

isCSV.Hostname Hostname ,

sysbigdatetime ( ) CreateAt FROM isCSV ;

CREATE OUTPUT STREAM osHANA SCHEMA schemaHANA

AS

SELECT * FROM Compute1 ;

CREATE OUTPUT WINDOW prepareOutput PRIMARY KEY DEDUCED KEEP ALL ROWS

AS

SELECT

osHANA.ScanTimestamp ScanTimestamp ,

osHANA.KID KID ,

osHANA.SID SID ,

osHANA.Hostname Hostname ,

COUNT ( * ) anzSuccess ,

'mail@domain.com' mailAddress ,

'sql_fragment success' mailSubject ,

'normal' mailImportance FROM osHANA GROUP BY osHANA.KID , osHANA.SID , osHANA.Hostname , osHANA.ScanTimestamp ;

ATTACH INPUT ADAPTER iaCSV TYPE toolkit_file_csv_input

TO isCSV

PROPERTIES

dir = 'E:/transfer2' ,

file = '*.csv' ,

dynamicMode = 'dynamicFileAndPath' ,

removeAfterProcess = FALSE ,

csvDelimiter = ',' ,

csvHasHeader = TRUE ,

pollingPeriod = 10 ;

ATTACH OUTPUT ADAPTER oaHANA TYPE hana_out

TO osHANA

PROPERTIES

service = 'ght' ,

sourceSchema = 'GIAIOT' ,

table = 'SQLSERVER.z_fragment.data::fragment2' ,

permutation =

'KID=KID:SID=SID:Hostname=Hostname:Table=Table:AnzRows=AnzRows:DataUsedKB=DataUsedKB:IndexUsedKB=IndexUsedKB:Fragmentierung=Fragmentierung:ScanTimestamp=ScanTimestamp:CreateAt=CreateAt'

;

ATTACH OUTPUT ADAPTER successMail TYPE smtp_out

TO prepareOutput

PROPERTIES

smtpHost = 'smtp.domain.com' ,

addressColumn = 'mailAddress' ,

subjectColumn = 'mailSubject' ,

importanceColumn = 'mailImportance' ,

fromAddress = 'mail@domain.com' ,

resendAttempts = 3 ;

-----------------------------------------------------------------------------------------------------------------------