SSIS Logging and monitoring

מעוניין לשתף?

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

When you add the log to a package, you choose the log provider and the location of the log. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

MSDN link explanation you can find here:

http://msdn.microsoft.com/en-us/library/ms140246.aspx

Here you can find how to enable logging in the package (I have also explained Daniel how to do it):
http://msdn.microsoft.com/en-us/library/ms141212.aspx

Integration Services includes logging features that write log entries when run-time events occur and can also write custom messages.
Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Here you can find how to configure your SSIS package:

http://msdn.microsoft.com/en-us/library/ms138020.aspx

SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:
Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written
“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping.

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

Here is a link for top 10 SSIS best practices:
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

Here is some code you can use in order to store the logging to Audit table:

The SSIS logging gives you the ability to find bottlenecks in your SSIS package like:

How much time it takes to validate packages
Trace each step and find out how long it takes
Gives you a tool you can use to log valuable events of the package.

כתבות נוספות שיעניינו אותך

אפריל 14, 2019   •

EXPERDA TEAM

תהליך מיגרציה לענן מובנה בשני תהליכים עיקריים

דצמבר 22, 2018   •

SQL server allows us to create multi-columned indexes. In most of today's systems –it's required.