How to log out SSIS package information in c#

Question

I have managed to get my application to run my test SSIS package but I want to be able to retrieve information that it returned I.E Time spent to run etc.

Here is how I am executing the package:

//EXECUTES THE PACKAGE.
Microsoft.SqlServer.Dts.Runtime.Wrapper.DTSExecResult pkgResults = pkg.Execute(null,container.Variables, null, null, null);

The pkgResults only returns me Success or Fail and not any other relevant information.

Is there a way I can get a Log of the information for me to store in a SQL database?

Answer 1

Ben,

I am assuming you want to know the time it took for the execution of the whole package or the tasks inside a package. Again, I would recommend you to use the ManagedDTS assembly if possible (instead of the wrapper assembly).

If you are using the ManagedDTS assembly, all container and task host objects inherit StartTime and StopTime properties from DtsContainer object. Thus in ManagedDts assembly you can do this (asuming pkg is a Package object in ManagedDTS assembly)

pkg.StartTime - pkg.StopTime

If you want the execution time for a specific executable in the package, you can still call the StartTime and StopTime properties on that specific executable (since it inherits from DtsContainer).

In the wrapper assembly, things are a little less straightforward. The StartTime and StopTime properties are available through the IDTSExecutionMetrics100 interface. You will need to cast your package object (or any other task/container) to that interface and then access the StartTime and StopTime properties.(asuming pkg is a IDTSPackage100 object in wrapper assembly)

(pkg as IDTSExecutionMetrics100).StartTime - (pkg as IDTSExecutionMetrics100).StopTime

ANd if you are looking for the execution durations of a specific executable inside a package, you would access that specific executable, cast it to the IDTSExecutionMetrics100 interface and then access the StartTime and StopTime.

The ManagedDTS assembly simplifies these things and all the relevant properties and methods are more intuitively available than through the Wraper assembly.