How to Log all actions and task execution times from SSIS.

Problem: How to Log all actions and task execution times from SSIS.

Solution: Integration Services (SSIS) Logging

In SSIS to keep a trace of what time the events are occurring by looking at the designer it is hard to keep a clock timer. As a feature functionality 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.

Integration Services includes the following log providers:

·         The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.

·         The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.

·         The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database.

·         The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

·         The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.

Example:

In this article, I am going to show we can use Logging of the package execution to a sql server database table.

Step 1: go to the control flow of the package and right click for properties or hit ALT+ENTER and select logging.

Step 2: From the logging window select the type of provider you want to choose for logging, in this I am going to use SQL Server Logging and from the configuration drop down I am going to create a database connection on where to store the logging table. This will create an sysssislog table in the database selected.

Step 3: select the details of the logging to be captured and in this case I am going to select pre-execute and post execute.

OUTPUT:

SELECT event,

                source,

                starttime,

                endtime,

                DATEDIFF(ms, starttime, endtime) AS timetakenforexecution

FROM [dbo].[sysssislog]

Results:

event	source	starttime	endtime	timetakenforexecution
PackageStart	Merge sources	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPreExecute	Merge sources	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPreExecute	Record Set DF	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPostExecute	Record Set DF	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPreExecute	Execute SQL Task	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPostExecute	Execute SQL Task	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
OnPostExecute	Merge sources	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0
PackageEnd	Merge sources	2017-06-02 21:56:36.000	2017-06-02 21:56:36.000	0