In this video, I'm going to demonstrate how to use the SSIS Log Analyzer tool to analyze complex and verbose SSIS logs quickly and easily.
This video is available with closed captions. To view closed captions, click CC on the video control bar.
Hello everyone. Welcome back to the SQL Server Video Series.
My name is Debarchan, and I work with the User Education Team at Microsoft for SQL Server Integration Services.
In this video, I am going to show you the usage of a tool called SSIS Log Analyzer, which is developed in Microsoft Technical Support Center.
This video assumes familiarity with basic knowledge of SSIS Packages, how they work, how to generate Verbose Package Log during Package Execution, and stuff like that.
In this video, you would learn to use the SSIS Log Analyzer tool for analyzing Complex and Verbose Package Execution Log quickly and efficiently.
It provides the user with a Graphical User Interface and displays each Log Record in a well-formatted and presentable manner.
For Analyzing Package Performance, the tool displays a graphical representation of the percentage of total Execution Time taken by each of the Package Tasks to execute.
If the SSIS Package is also provided along with the Log File, the Log Analyzer can even simulate the Package Execution and provides the user an option of setting and managing Breakpoints and examine each Log Record Values during Package Execution.
Work is being done on a feature called Auto Analysis, which would plug in Intelligence into this tool where it would be smart enough to parse through the Log File and provide the user with a set of suggestions, workarounds, potential problems in the Package, and things like that.
Currently, SSIS Log Analyzer supports SQL Server 2005, 2008 and 2008 R2. This tool is freely downloadable from ssisloganalyzer.codeplex.com.
So with this, we move into a small demo that I have here.
So this Package takes a while to complete, so I’ll just start the Execution.
If we see that this Package loops through a set of CSV files that are located in this folder, so here you see the set of CSV files, the Script Task parses each file, detects the data in it, and this Execute SQL Task creates the appropriate schema on the SQL Server side.
Finally, there’s a Bulk Insert Task that actually loads the data from each of the CSV files to the corresponding SQL Server Tables.
So I have created a Log File.
I have chosen the location C:\SSISLog.txt as the path for my Verbose Log and I have a couple of connection managers to connect to my SQL Server as well as to the FAT files.
So now, the Package Execution is complete.
I just bring up the SSIS Log Analyzer tool, start a new Analysis Window, and here is the place where you have to provide the path of the Package.
So I know where my Package exists, so I’ll just go ahead to that location, select the Package and as we saw that the Log is created at C: drive root, the name of the log is ssislog.txt.
The format of date is MDY in my machine, so I’ll just change it to MDY and say Load.
So as you can see, the Log Analyzer presents us with the Manual Analysis tool, so the people who have the experience of going through the Verbose SSIS Log Files through Text Editor, they can imagine how painful it could be to go through each of the Log Records in a Standard Text Editor.
So this Manual Analysis tab shows each Record in a grid.
You can double-click on any of the grid Records to bring the details of that.
You can navigate up and down, too; back and forth to a Next and Previous Records, even copy the Log Record details in the Clipboard.
There is a Filter that you can apply.
For example, if you say Source is Bulk Insert Task or for example, say, Execute SQL Task and say Apply.
So you will see it filters only on the Execute SQL Tasks.
Similarly, you can Add or remove additional columns you may want to be displayed, and there is also a Query Editor Window.
So here, you can write Queries like Event = 'Diagnostic'. Say, Execute. So it filters only on the Diagnostic Events.
So this is what we get in the Manual Analysis tab.
The next tab, we move over, is the Performance Analysis.
So you see it shows a graphical representation of the time taken by each of the Package Tasks.
For example, the SQL Connection took forty-three seconds to open up.
The Bulk Insert took forty-five seconds, and so on.
So imagine a Package running under production with a heavy load of data and it’s taking a lot of time to execute.
Through this Performance Analysis tab, you can easily identify which of the Tasks are consuming most of the Package Execution Time.
So we move on to the next tab, that is a Debug tab here and this tab actually simulates the entire Package Execution.
Please note that you have to provide both the Package as well as the Log File to get this Debug tab and simulate the Package Execution.
If we don’t have the Package, we can just select Analyze Log File Only, and say Load.
So you’ll see that the Debug tab is no longer visible. So I’ll just bring that back in.
Okay, so now that we have the Debug tab, you can see, we can just step through each of the Package Events and actually simulate the Package Run Time.
So you see it loop through all the Events as if the Package is executing currently.
I can pause it any point of time and examine the details of the Log Record at that point.
I can even set Breakpoints here and when I Execute, it will halt at that Breakpoint.
It will blink and highlight that the Breakpoint is hit and again, you can just go ahead and check the Log Entry Values.
You can also right-click on any of these Events; I’m sorry, on any of these Tasks and say Show All Events For This Task.
So it will bring up the Events particularly pertaining to that specific Task only.
So I just go ahead and Execute it. It will take some time to complete.
It will scan ‘til the end of the Log File and report us that it has reached the end of that Log.
So the last step, the Auto Analysis step, so this is the one where work has been going on.
So it will have some Auto Analyzers that will have some kind of Artificial Intelligence fed into them. So they will part of the Log Files.
They will see the Common Errors or the Common Log Messages and provide the users with the Analysis results.
So right now, we don’t have anything, but it will show us in this way.
Okay, so in this video, you learned about the benefits of using the SSIS Log Analyzer to examine Complex Package Logs.
You also viewed a demonstration where we analyzed the Execution Log of a Sample Package to leverage the different features of this tool.
Hope you find this video of use and revisit this site for more SQL Server videos.
If you have found this video helpful, please do not forget to rate it.
Big thanks to Carla and the entire User Education Team for their help in getting this done. Thanks.