您可以监控的SSIS包的执行，在BIDS，因为它在服务器上运行？(Can you monitor t

Although my title states my current goal, I am open to alternative solutions. In short, I have a series of SSIS packages that are scheduled to run on a nightly basis, on our SQL Server machine.

Due to various updates that happen in the ether of my corporate-IT, some times these exports brake and the process will stop working, in the middle of the job. To troubleshoot this process, I fire up BIDS on my workstation and restart which ever process fails. This is useful because it's been my experience that any error generated from within BIDS are much more useful than anything I've found from within SQL Server/Package Execution History or the servers event logs.

Historically, my problem has been that not all problems will occur in BIDS, but they will, consistently on the server. These issue have been painful to diagnose and have cost me a lot of time.

As such, what I would like to do is publish my package to the SSIS server, start the server instance from BIDS and monitor the project as it runs. Is this possible?

If not, is there something else that I can do so that I can monitor the internal steps as the process executes?

I fear that none of this may be possible but I have to ask. It will make my debugging and troubleshooting life so much easier.

Answer 1:

Possible option:

You need to make use of Logging feature in SSIS. It allows you to configure the events for which you would like to capture the messages. I usually prefer the log OnWarning and OnError to keep track of all the warning and error messages that occur in the package. You have various provider to save the logging data. I prefer to use SQL Server so that I can query the logging information.

Logging options shown from SSIS 2012:

To enable logging in a package, you need to click the package Business Intelligence Development Studio (BIDS) if you are developing packages in SSIS 2005 - 2008 R2 or SQL Server Data Tools (SSDT) if you are developing packages in SSIS 2012.

Click SSIS menu and then click Logging...

You will see the Configure SSIS Logs dialog.

On the left side, you can check the package or individual tasks to log the event data.

On the Providers and Logs tab, you can select an appropriate provider to which you can save the log information. The below screenshot shows that event information are captured in a SQL Server database using the connection manager OLEDB_PracticeDB.

On the Details tab, you can select which events you would like to capture. The below screenshot shows that I am capturing the following events.

OnError
OnInformation
OnTaskFailed
OnWarning

Thanks to @William Todd Salzman for recommending OnTaskFailed event

Sample package illustration:

Let's say we have a package named SO_15004109.dtsx with a Data Flow Task and Script Task. Data Flow Task is just a dummy with no components inside.

Script task has the following code in Main method to fire custom information, warning and error messages so we can observe how it is captured in the logging data source. The code is written for SSIS 2012 so you may need to alter it for SSIS 2005. I chose VB.NET instead of C# because you have tagged this question under sql-server-2005 and SSIS 2005 only supports VB.NET.

Script Task code in VB.NET for SSIS 2005 and above.

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
#End Region

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()

        Dim fireAgain As Boolean = False
        Dts.Events.FireInformation(101, "Custom Script Information", "This is a test information message.", String.Empty, 0, fireAgain)
        Dts.Events.FireWarning(201, "Custom Script Warning", "This is a test warning message.", String.Empty, 0)
        Dts.Events.FireError(201, "Custom Script Error", "This is a test error message.", String.Empty, 0)

        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

If we execute the package, it will fail because we raised an error within the Script Task.

If you navigate to the data source where you logged the errors, you will notice that SSIS creates a table to log the information if you chose SQL Server logging provider. Below table lists the logging table that SSIS creates in the SQL Server database chosen to be the log provider.

SSIS Version   Log table name    Table type
-------------- ----------------  ----------
SSIS 2005      dbo.sysdtslog90   User
SSIS 2008      dbo.sysdtslog100  User
SSIS 2008 R2   dbo.sysssislog    System
SSIS 2012      dbo.sysssislog    System

Below query was executed in the database to view the events captured by this sample package. You will notice some messages twice because the events are logged for each containers and tasks. The table is named dbo.sysssislog because the package was created in SSIS 2012.

select id, event, source, message from dbo.sysssislog;

Personal experience with logging:

I have had fairly good success in just viewing the logging error messages to understand what went wrong. Debugging packages in production environment is not advisable, in my opinion. However, capturing log events is preferable.

When I worked in SSIS 2005 and 2008, I have created SSRS based reports that queries the log table to generate daily report of the job executions and send a PDF attachment to persons of interest.

Things have improved in SSIS 2012 that the tool comes with in-built reporting capabilities with Integration Services Catalog that creates a database named SSIDB.

Answer 2:

在我目前的演出中，我们使用从语作品一款名为开始BIxPress ，其中包括工具注入日志代码到现有的SSIS包和监视正在运行的包，因为他们执行。它并不便宜，但我们发现它有用。（特别是因为它意味着我们没有推出我们自己的监管码...）