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 ;
-----------------------------------------------------------------------------------------------------------------------