Hello and welcome to our latest blog post here on BIDA Brains.
I am pleased you have come back to see our latest blog post.
Thank you.
This is one for the “techies” in our audience. Only of great interest to people who are responsible for running schedules of ETL processing for their companies or customers.
One of the things that has to happen is that the ETL support “techies” have to be informed when the ETL fails so they can go and fix it in the middle of the night while the business users are fast asleep in their nice warm beds!
The standard way of informing an ETL support “techie” that a batch has failed, or has completed, is by email.
Of course, BIDA has the option of sending an email on failure.
However, this requires the email message be put on every line of the schedule.
So in practice what we do is we put emails in to long schedules at important sync points and we put an email at the end of the schedule.
Then the absence of the email is the prompt to go and check the schedule.
Of course, we could have error emails being sent as well, we just don’t really do that in practice.
With the advent of Office 365 some of our customers wanted to move the email sending over to Office 365.
This is a little different to the standard smtp email server and so we had to work out how to make it work.
Since we had to work out how to make it work we thought it was worthy of a blog post so others could benefit from our piece of work.
Our scheduler is very reliable.
We have had customers run more than 6 months without an ETL failure.
With Office 365 the first thing you need to do is to convert the email password to be used to an encrypted text string to be placed in a file.
This string will then be used for authentication with the O365 smtp mail server.
The way you do this is to create a power shell command. So let’s call it:
run-this-as-a-power-shell-command.ps1
In this power shell command you put entries something like this.
You do not need your email address, you will be prompted for your password and it will be encrypted, it will not be checked by this process.
Obviously the “Out-File” is where the encrypted password will be written to:
Read-Host -Prompt "Enter your email password" -AsSecureString | ConvertFrom-SecureString | Out-File "M:\A_BIDA_Hosting\CUSTOMER\S01-Schedule\m-neacsu-email-password.txt"
Having put that command in to the ps1 text file you just right click and say run as a power shell command.
The power shell window will open with the
"Enter your email password"
prompt. You type in your password and it will write the encrypted password to that file.
The contents of that file will be very long string. The first characters will look something like this.
"01000000d08c9ddf0115d1118c7a00c04fc297eb010000009b79e308860dc44fbcbdb8f4e16ba3490000000002000000000003660000c0000000100000..."
This is what an encoded password looks like for Office 365.
Now you want to create the power shell command that will actually send your email.
You can have one such command for each different email you want to send.
As I said, for long running ETL we usually send emails at important sync points so we know that progress is being made. And then we have one email at the end.
So next you create an power shell command that will be the actual command executed at run time in the scheduler. For example you could call it:
run-m01-send-end-etl-email.ps1
And the contents would be something like this:
$AdminName = "mihai@somedomainname.com"
$Pass = Get-Content "M:\A_BIDA_Hosting\CUSTOMER\S01-Schedule\m-neacsu-email-password.txt" | ConvertTo-SecureString
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Pass
Send-MailMessage -From mihai@somedomainname.com -To mihai@somedomainname.com -Subject "BIDAWeekDayBatch001 - Batch Completed" -Body "BIDAWeekDayBatch001 - Batch Completed" -SmtpServer smtp.office365.com -Credential $cred -UseSsl -Port 25
You store this command usually in the same directory as the password and the power shell commands to run this command.
Now, in order to run that power shell command from inside the BIDA Scheduler you need to create a mechanism by which it can be run.
BIDA executes commands using the “command” command inside the scheduler program.
So the command that is passed to the shell of the external operating system has to be a command that is acceptable to the external operating system.
It can not be a power shell command.
So we create a command in a file named something like this:
runPowerShellCommand.cmd
And inside the command file we put a statement something like this:
PowerShell.exe -Command %1
So now we have to have a way of putting
runPowerShellCommand.cmd
in to the scheduler to be run when we want it to run. In this case we want it to run at the end of the ETL to send an email notifying the support person that the ETL has ended.
So, in the scheduler in the process commands section you create an entry like this:
Process Name = bidapowershellexe01
Process Command = ####Scheduler_Signal_Files_Folder####\runPowerShellCommand.cmd ?1
Comment = Run a powershell command
Notice you must use a “?” here and not a “%” sign.
You can use variable substitution in the Process Command field as we have done here. And you invoke the
runPowerShellCommand.cmd
passing it one parameter.
Now this parameter is resolved by BIDA, not the operating system, and to avoid confusion BIDA uses “?” for parameters.
So this Process Name can be called from any line inside the schedule itself and passed a parameter of a power shell command and it will execute that power shell command.
So, then, in the schedule itself you can put:
Process Command = bidapowershellexe01
Process Command = bidapowershellexe01
Parameter = "####Scheduler_Signal_Files_Folder####\run-m01-send-end-etl-email.ps1"
Yes, we do now repeat the Process Command.
We used to have the ability to have two different values in the batch command line of the scheduler.
Now we tend to use the same for such commands as this because there was no real value in having them be different.
What this command in the actual schedule will do is to execute the file
run-m01-send-end-etl-email.ps1
by using the power shell command.
So this will then be the step that actually sends the email at the end of processing.
So there you have it.
This post has shown you how it is that we send emails via O365 accounts from inside the BIDA scheduler.
Of course, smtp emails are quite simple and it is quite well understood how to send them via a command line.
With the use of O365 it’s a little trickier because we have to use power shell and we have to provide the encrypted password.
This is why I thought it was worth my while to put this blog post up for you to show you how this works!
Of course, our scheduler has turned out to be the backbone of our run time processing.
It is more than 20 years old now and has passed the test of time.
Over the years it has proven itself to be very reliable.
We start it up at system startup time and it just runs.
We recommend putting the scheduler on the same machine as the database for the schedule.
This has proven very, very reliable.
The only real issue left with the scheduler is that if the machine crashes so hard in the middle of a batch that the scheduler did not have time to write out the fact that it was going down?
Then manual intervention is needed to get the scheduler restarted because it did not record the fact it was crashing.
If the DBA performs a shut down of the database properly then BIDA will notice that the database is being shut down and record those processes that have not been completed and the restart will find them and run them.
We also provide a way to stop the scheduler properly using a signal file.
We recommend that when the host machine is being taken down for maintenance the scheduler is stopped properly.
Today, servers crash so rarely this is not an issue.
BIDA just runs and runs like the energiser bunny.
It has parallelisation features that mean we can take a DataStage schedule of jobs and actually run it faster than DataStage can.
That’s why we have the DataStage interface, to do exactly that!
The BIDA scheduler also comes with SQL Server Report Services Reports that expose the schedules and the schedules in progress so that ETL support “techies” can see where any specific schedule is up to.
With schedules commonly having more than 5,000 steps reports for where things are up to are quite useful.
I thought it would be polite and helpful to write a short blog post to tell you how we invoke PowerShell and how we can send commands, such as sending an email via O365, using PowerShell.
And with that?
We hope you liked this blog post.
If you have any questions or comments please feel free to get in touch with us.
My details are on the contacts page.
Thank you very much for your time and attention.
We really appreciate you dropping by to read our blogs.
Best Regards.
Mihai Neacsu
Business Development Manager.
The BIDA Team.
Ask Us Anything
You are welcome to ask us any question you would like below on our contact form.
We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.
