Product:
Cognos TM1 10.1.1 or TM1 10.2.2
Windows 2008 R2 Server
Problem:
How do I send a mail to the user that a TM1 Turbo Integrator (TI) process have finished?
Think you are in a TM1 project, and you want the TM1 Super user to get a mail when the csv file with data has been loaded into the DB2 database.
Possible Solution:
You can use the BLAT utility, found her http://sourceforge.net/projects/blat/files/
Ensure that you have a mail server that accepts SMTP mail from the TM1 server.
Create a empty folder on the TM1 server, e.g. d:\tm1 data\scripts
Copy all the BLAT files to the empty folder.
blat.dll
blat.exe
blat.lib
blatdll.h
If possible add the folder where BLAT.EXE are to the Windows Path variable.
Recommended is to use the BLAT install function to store the user and password to access the SMTP in the Windows registry, there it will be encrypted.
Create a cmd file named installblat.cmd with this content:
call “D:\tm1 data\script\blat.exe” -install smtp.bredband.net roger-mailname@bredband.net 3 25 Peo bbbbbbb cccccccc
REMark where bbbb is replace with the username and ccccc is replace with the password
for a account that are allowed to send SMTP mail on the mail server.
SMTP name should be replaced with the name of your SMTP server.
the roger-mailname should be replaced with your Sender address, that should be known to the SMTP mail server.
http://www.blat.net/examples/install&profile.html
Run the installblat.cmd to get the values stored in Microsoft Windows.
This create the values inside Microsoft Windows registry at key;
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Public Domain\Blat\Peo]
“SMTP server”=”smtp.bredband.net”
“SMTP Port”=”25”
“Try”=”3”
“Login”=”xxxxxxxxx”
“Pwd”=”xxxxxxxxxx
“Sender”=”xxxxxxxxx@bredband.net”
This is stored under the profile key Peo, you can use any name for the profile, but it have to be the same name used in the other scripts.
Create a recipient.txt file that contains the names of the receiver of the alert mail.
Should be simple, with only the mail name one each line.
roger@konsult.company.se
frango@cmpny.com
Create a startblat.cmd file to start the blat function, it should contain this text:
call “D:\tm1 data\script\blat.exe” message.txt -tf recipient.txt -subject “Mapping file loaded” -p Peo
Above two is optional, you can also create the startblat command in the TI process and add the recipient to the code.
You can create a message.txt file before if you want, but to add the filename of the file that is read by TM1, we have added the creation of the message file to the TI process, so you can adjust the variable to get the filename from some other TM1 process.
You need to create two dimensions to handle the adjustable settings in a settings cube.
Create the dimension Sys.EnvironmentVariables with the elements Mottagare, MailSender, MailSubject, ScriptPath.
Create the dimension Sys.Measures
with the values String and Number.
Create a Sys.Settings cube from above two dimensions, in this cube can you enter name of the sender and other useful information like path to the files, that can change when you move the TM1 application to a new server.
For example at ScriptPath you should enter D:\TM1 data\Scripts\ in that cube.
And at Mottagare you should enter the name of the recipient of the message in a SMTP mail format like name@company.com. Please fill in the other values accordingly.
Create this code in the PROLOG tab;
#————————
# Begin Code date
#————————-
# set to not get ” in the text file you write to disk
DataSourceASCIIQuoteCharacter = ”;
# to add ” in the string you need a variable
# more info: http://www.robelle.com/smugbook/ascii.html
q=char(34);
# for the test the name is set to variable, it can be collected
# from other process steps before.
sFile = ‘SOLVENS PROTOTYPE’;
# Get path to script (bat files) from value in Sys Settings cube
# this is good if you need to move your TM1 app to other server,
# to have all “paths” in a sys.settings cube
sPathBlat = CellGetS (‘Sys.Settings’, ‘ScriptPath’, ‘String’);
sSMTPServer = ‘smtp.bredband.net’;
# Set the message to be sent to the users
sMessage = ‘The file ‘| sFIle |’ have been loaded to the TM1 application’;
#
# get value from cube named sys.settings on the
# label Mottagare in column String
sRecipient = CellGetS (‘Sys.Settings’, ‘Mottagare’, ‘String’);
sSender = CellGetS (‘Sys.Settings’, ‘MailSender’, ‘String’);
sSubject = CellGetS (‘Sys.Settings’, ‘MailSubject’, ‘String’);
# write message to text file that later can be used by BLAT
asciioutput ( sPathBlat |’message.txt’, ‘The ‘| sFile |’ file have been loaded into the TM1 Solvens II solution’);
# call the cmd (bat file) to start BLAT
##executecommand ( sPathBlat | ‘startblat.cmd’ , 0 );
# create the DOS command line
# more info http://www.blat.net/syntax/syntax.html
#
sDOS = ‘blat.exe message.txt -to ‘| sRecipient |’ -subject ‘| sSubject |’ -p Peo’;
# only for debug purpose – write variable to file
###asciioutput ( sPathBlat |’tiprocessdebug.txt’, sDOS );
executecommand ( sPathBlat | sDOS, 0 );
#——————————-
# end of prolog code
#——————————-
Save the process as Sys.SendMail.
Fill in your values for sender and SMTP server and then you can test run it.
Above prolog code show two different way of calling the BLAT.EXE, direct from code, or if you change the ## it will call the CMD file startblat, than then will call the BLAT.EXE file.
To check the value in a TM1 Variable, you can write it to a file, this is why we have the debug line ###, unmark the ASCIIOUTPUT to create a txt file that contain the value of the sDOS variable.
More information:
http://www.blat.net/examples/install&profile.html
http://sourceforge.net/projects/blat/files/
http://www.blat.net/
http://blog.tm1tutorials.com/tag/asciioutput/
http://www.exploringtm1.com/tm1-archive-ti-process/
Always check files you download from Internet fro malware with your anti-virus software.