CODEWITHSUNDEEP
debuggingvisual-studio-2012ssissql-server-2012break
Ehryk
Ehryk

Reputation: 1990

How can I log the termination/any end of an SSIS package?

I've got an SSIS package (targeting SQL Server 2012) and I'm currently debugging it. What I'm after is how to log that the SSIS package has finished or stopped by any methods.

The closest ones look to be 'OnExecStatusChanged', 'OnPostExecute', and 'OnPostValidate' however NONE of these provide any log messages when I break execution in Visual Studio.

Here's a screenshot:

SSIS Logging Options

I suspect the answer may be "you can't", but I want to see if there are perhaps more exotic solutions before I give up.

Upvotes: 0

Views: 841

Answers (2)

user4196683
user4196683

Reputation:

You do have two options available that I can think of.
One has been highlighted above in using the pre and post execute functions. If you were to use this solution I would recommend using a table (Dim_Package_Log?) and inserting to this table using a stored procedure on pre and post execute.
Clarification: This wont catch package breaks, just start, end and errors.

As you rightly identified though this would not record package breaks. To capture this I have implemented a view that utilises two tables.

SSISDB.catalog.event_messages

SSISDB.catalog.executions

If you do some "exotic" joins you can utilise the execution_status from executions and the messages from event_messages to find the information you want.

I can't remember which msdn page I found it, but this is what the execution_status means in catalog.executions

The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).

Clarification:

Below is a sample line of what SSISDB.catalog.executions outputs for each package execution from a Job:

43198   FolderName  ProjectName PackageName.dtsx    NULL    NULL    NULL    NULL    10405   GUID    SERVICEACCOUNTNAME  0   200 2015-02-16 00:00:03.4156856 +11:00  20  18  7   2015-02-16 00:00:05.4409834 +11:00  2015-02-16 00:00:58.4567400 +11:00  GUID    SERVICEACCOUNTNAME      10324   NULL    NULL    ID  SERVER  SERVER  16776756    3791028 20971060    8131948 2

In this example there is a column with a value of 7. As detailed above this status changes based upon the end state of the execution of the package. In this case, successful. If the package breaks midway it will be captured in this status.

Further information regarding this ssidb capability is located at this MSDN page.

Upvotes: 1

Ako
Ako

Reputation: 1221

I know that is a partly answer. What is covered here is to detect that a package is finished by an error of success. This you can do by calling the package from an another parent package.

Playing with package result

But if the package is forced to stop then this won't have any effect.

Upvotes: 0

Related Questions