Note: You can listen to the blog post on the video or read the blog post.
Hello and Welcome.
I am Esther.
I am Peters A I Assistant to create voice overs.
I will simply read Peters blog posts, so that you have a choice of reading the blog post, or listening to my voice.
Hello and welcome Gentlemen.
As I have recently announced on my channel.
I will be doing some opinion pieces and response videos.
In this blog post I wanted to talk about a recent finding with SQL Server Integration Services.
All around the world millions of people use Integration Services.
It’s not the worlds greatest ETL product, but it is good enough for a lot of situations.
This is especially true of smaller sites with less extensive needs in their ETL tool.
However, one of the problems that all the ETL tools have, and SSIS is no exception.
It is the problem of creating multiple streams of parallel processing which include semaphores to close out one stream of parallel processing and to start another.
Another problem having set up such a package is to be able to restart it properly after a failure.
If you are the owner of a data warehousing company that implements ETL using SSIS?
You know these two problems.
Of course, everyone knows I started in the IBM Mainframe world and IBM solved these problems in the nineteen seventies.
When I joined IBM in nineteen eighty six one of the many things I had to learn was how to write very complex Job Control Language jobs to be able to handle very large and complex batch loads.
So when I wrote the scheduler for See TL I included what I had learned all those years ago.
Why is this important to you as a man who runs his own data warehouse consulting company using SSIS as your standard ETL software?
In the last week or so I have had to do a crash course on SSIS.
One of the questions that occurred to me is whether it was possible to run SQL Server Jobs under SQL Server Agent using the see TL scheduler.
What is needed is the ability to run the job, detect errors that are fatal errors, and then to stop the stream if a fatal error has been detected.
Of course, once the error is fixed, in see TL the restart does not require any alteration to any code.
You just restart the batch.
This can be done from a Report Services Report now in one of the versions I am supporting.
But rest assured.
Last week I tested the ability of the see TL scheduler to execute a stored procedure, which then executes a job, which can detect if the job fails, and then return a non zero return code to the see TL scheduler to stop the job stream.
What does this mean for you mister, I own a data warehouse consulting company?
It means with SSIS you can now do the following, for free, with our see TL scheduler.
Consider the normal ETL batch.
The first thing you need to do is to extract all the source data from all your sources and land the data into the landing area.
Given you have SQL Server you want to run parallel streams to do that to make maximum use of the network bandwidth and your CPUs to make the overall process as fast as possible.
Say you have two hundred source tables from your source system.
This is quite normal for business central data warehouses.
So you would want at least four parallel streams on a sixteen core SQL server.
You would balance out the big tables with the smaller tables in terms of number of steps per stream.
But just for this blog post lets say all extract and landing jobs are the same so you want four streams of fifty jobs.
You need to do some initialisation for the overall batch.
So you will have a group oh oh one.
And then you need a semaphore group oh oh two.
And then you can have four groups oh oh three through to group oh oh six.
Those four groups will be able to run at the same time.
And now they can be SQL Server jobs in SQL server agent running SQL Server Integration Services Packages.
Then you have another semaphore group called group oh oh seven.
Group oh oh seven can not run until all four prior groups have completed.
If one group has a failed command the other three groups keep running.
When the error is fixed the support person can simply ask for a restart and the scheduler will restart just the failed stream and not touch the other in progress streams.
We called this in flight restart of partially failed batches.
Not even IBM had this back in the day.
Certainly SQL Server does not have it today despite the fact I wrote my scheduler in two thousand and three.
Once all four groups are finished and you have processed your two hundred inputs you will want to process your delta detection and send data from the landing area to the staging area.
Only this time you might want eight parallel groups.
So you create groups oh oh nine to oh oh sixteen and you run eight parallel groups of SSIS packages to perform your delta detection for your two hundred input tables.
And so this goes on.
Let us say four more groups for dimension table processing.
Let us say four more groups for fact table processing.
And then a last semaphore group and a final group for finalization processing of the batch.
Pretty soon you are up to around thirty processing groups that have to all be defined and managed.
The wonderful thing about the see TL scheduler is that you can move processing from group to group between the batch runs as easily as cut and paste in a spreadsheet because that is where the schedule is maintained.
Over the period of a week or so you can balance out the processing in the streams to make the overall batch stream run faster by having the SQL Server loaded to the maximum possible level all through processing.
When we were doing this with Data Stage we were able to run the data stage batches thirty percent faster than Data Stage was able to run the exact same batch workload.
We haven’t tested it to that level with SQL Server and Integration Services.
But I would be surprised if we were not able to cut thirty percent off the run time of a sizable ETL stream.
In the past we have reduced ETL run times by up to seventy percent when we replaced ETL systems built with stored procedures.
So there is every chance that just using our scheduler will reduce run times by up to thirty percent.
I would be interested to see someone try it out.
As a man who owns his own data warehousing consulting company using SSIS?
You know the amount of time it takes to run ETL streams and you know that for a lot of that time the SQL Server is not that heavily loaded.
See TL will load up the SQL Server and cut your run times.
See TL will mean that when a batch fails there is no manual change required to any packages to restart the batch and run it to completion.
And all that is available for free from my web site.
You can have the see TL scheduler for free.
And you can test and run exactly what I have talked about for free.
If you like it?
Keep it and use it.
If you want a version of just the scheduler with your own company brand name on it?
Please just ask.
We will work out a price.
And please remember, you can just buy a branded version of see TL from a partner or you can get one for yourself for the very low price of ten thousand euros or dollars.
I just want it to be a nice round number either way.
So.
In summary?
Creating large batch schedules for processing large volumes of data and tables for data warehouses takes quite a bit of effort in SSIS.
If you have production accounts doing this?
You can have my see TL scheduler for free.
My see TL scheduler can now schedule SSIS packages properly.
Including stopping processing for serious errors that require processing to stop.
The restart is as simple as going to a report services report and selecting the failed batch and setting the report parameter to restart the batch to yes.
All in all?
A much simpler way to run your already existing SSIS ETL system.
You will get faster overall run times, fewer failures, and fewer days when the data is not ready for your users at nine in the morning.
And with that?
I hope you found this blog post interesting and informative.
Thank you very much for your time and attention.
I really appreciate that.
Best Regards.
Esther.
Peters A I Assistant.
