Multiple Log Providers
- From: A. Robinson <ARobinson@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 18 May 2007 10:43:01 -0700
I'm hoping someone on here could provide some insight into something I'm
trying to accomplish within SSIS.
We have dozens of SSIS packages that run over night. By default, these
packages do not have logging enabled. We have a package called "RunMaster"
that essentially does the following:
1.) Itertates through all the packages.
2.) Programmatically enables logging.
3.) Logs the output from the package to custom tables (not sysdtslog90).
What I'd like to do is enable the RunMaster SSIS package to log to two
different sets of tables - the custom ones and the default sysdtslog90 table
in MSDB.
Is this possible? I've included the VB script that does all the work. I
pretty much understand 95% of what it's doing, but not enough to go in and
tinker.
Again, if anyone can provide any insight, it would be greatly appreciated!
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Reflection
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
'Imports WFHM.ServicingIntegration.MISRptStartup
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables,
events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to
indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim strPathPkg As String
Dim pkgChild As New Package
Dim app As New Application
Dim execCtrl As Executable ' An Executable is a Work Flow control in
DTS
' Load package
strPathPkg = Dts.Variables("PackageFolder").Value.ToString +
Dts.Variables("ChildPkgName").Value.ToString
app.PackagePassword =
Dts.Variables("PackagePassword").Value.ToString()
pkgChild = app.LoadPackage(strPathPkg, Nothing)
pkgChild.FailParentOnFailure = True
pkgChild.MaximumErrorCount = 1
' Set vairables
If Dts.Variables("parmInt1Name").Value.ToString <> "" Then
pkgChild.Variables(Dts.Variables("parmInt1Name").Value.ToString).Value =
Dts.Variables("parmInt1Val").Value
End If
If Dts.Variables("parmInt2Name").Value.ToString <> "" Then
pkgChild.Variables(Dts.Variables("parmInt2Name").Value.ToString).Value =
Dts.Variables("parmInt2Val").Value
End If
If Dts.Variables("parmSTR1Name").Value.ToString <> "" Then
pkgChild.Variables(Dts.Variables("parmSTR1Name").Value.ToString).Value =
Dts.Variables("parmSTR1Val").Value
End If
If Dts.Variables("parmSTR2Name").Value.ToString <> "" Then
pkgChild.Variables(Dts.Variables("parmSTR2Name").Value.ToString).Value =
Dts.Variables("parmSTR2Val").Value
End If
' We do not use Serializable, because it is too system intensive.
If the developer has left
' the default options, change them
If pkgChild.IsolationLevel = IsolationLevel.Serializable Then
pkgChild.IsolationLevel = IsolationLevel.ReadUncommitted
pkgChild.TransactionOption = DTSTransactionOption.Supported
End If
' Set Checkpoint to be used
'pkgChild.CheckpointFileName =
Dts.Variables("PackageFolder").Value.ToString + "CheckPoint\" +
Dts.Variables("ChildPkgName").Value.ToString.Replace(".dtsx", ".chkpoint")
'pkgChild.CheckpointUsage = DTSCheckpointUsage.IfExists
'pkgChild.SaveCheckpoints = True
pkgChild.CheckpointUsage = DTSCheckpointUsage.Never
pkgChild.SaveCheckpoints = False
' Initialize Logging
Dim logClass As LogClass = New LogClass()
pkgChild.LoggingMode = DTSLoggingMode.Enabled
pkgChild.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion
pkgChild.LoggingOptions.EventFilter = New String() {"PackageEnd",
"PackageStart", "OnProgress", "OnPreExecute", "OnPostExecute", "OnError",
"OnWarning", "OnInformation"}
' Set each task in package to Log
Dim evProvider As EventsProvider
For Each ex As Executable In pkgChild.Executables
evProvider = CType(ex, EventsProvider)
evProvider.LoggingMode = DTSLoggingMode.UseParentSetting
evProvider.FailPackageOnFailure = True ' For CheckPoint
' We do not use Serializable, because it is too system
intensive.
evProvider.IsolationLevel = IsolationLevel.ReadUncommitted
Next
' Run child package
logClass.StartLogging(Dts.Variables("ChildPkgName").Value.ToString, _
Convert.ToInt32(Dts.Variables("RunPackageID").Value), _
pkgChild.ID)
pkgChild.Execute(Nothing, Nothing, Nothing, logClass, Nothing)
If pkgChild.Errors.Count > 0 Then
logClass.EndLogging("Failure", pkgChild.ID)
Else
logClass.EndLogging("Success", pkgChild.ID)
End If
If pkgChild.Errors.Count > 0 Then
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
End Sub
End Class
Class LogClass
Inherits LogProviderBase
Implements IDTSLogging
' Variables.
Dim _fireAgain As Boolean = True
Dim _PackageID As Int32 = 0
Dim _PackageName As String = String.Empty
Dim _connLogDB As OleDbConnection
Public ReadOnly Property Enabled() As Boolean Implements
IDTSLogging.Enabled
' Required for Implement
Get
Return True
End Get
End Property
Public Function GetFilterStatus(ByRef eventNames() As String) As
Boolean() Implements IDTSLogging.GetFilterStatus
' Required for Implement
End Function
Public Sub Log(ByVal logEntryName As String, ByVal computerName As
String, ByVal operatorName As String, _
ByVal sourceName As String, ByVal sourceID As String, ByVal
executionID As String, ByVal messageText As String, _
ByVal startTime As Date, ByVal endTime As Date, ByVal dataCode
As Integer, ByRef dataBytes() As Byte) Implements IDTSLogging.Log
If String.Compare(_PackageName, sourceName, True) <> 0 Then
Select Case logEntryName
Case "OnPreExecute", "OnPostExecute", "OnProgress"
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID)
Case "OnError", "OnWarning"
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID)
Case "OnInformation"
If messageText.IndexOf(" wrote ",
StringComparison.CurrentCultureIgnoreCase) >= 0 _
AndAlso messageText.IndexOf(" rows",
StringComparison.CurrentCultureIgnoreCase) >= 0 Then
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID)
End If
End Select
End If
MyBase.Log(logEntryName, computerName, operatorName, sourceName,
sourceID, executionID, messageText, startTime, endTime, dataCode, dataBytes)
End Sub
Private Sub LogStep(ByRef logEntryName As String, ByRef sourceName As
String, ByRef startTime As Date, ByRef strMessage As String, ByRef sourceID
As String)
Dim strExecResult As String
If _connLogDB.State = ConnectionState.Open And _PackageID > 0 Then
Try
Dim cmdSQL As OleDbCommand = _connLogDB.CreateCommand()
If String.IsNullOrEmpty(strMessage) Then
strMessage = String.Empty
Else
strMessage = strMessage.Replace("'", "''")
End If
cmdSQL.CommandText = "Exec dbo.insPackageStep " _
+ "@PackageRunID = " + _PackageID.ToString + "," _
+ "@StepName = '" + sourceName + "'," _
+ "@SourceID = '" + sourceID.ToString + "'," _
+ "@Event = '" + logEntryName + "'," _
+ "@Message = '" + strMessage + "'"
cmdSQL.ExecuteNonQuery()
Catch ex As Exception
Dts.Events.FireInformation(0, "LogClass:LogStep",
ex.Message, "", 0, True)
End Try
End If
End Sub
Public Sub StartLogging(ByVal strPkgName As String, ByVal
intPackageRunID As Int32, ByVal strSourceID As String)
OpenLog()
' save class variables
_PackageID = intPackageRunID
_PackageName = strPkgName.Replace(".dtsx", "")
' get rid of any path in package name
If _PackageName.IndexOf("\") >= 0 Then
_PackageName = _PackageName.Substring(_PackageName.IndexOf("\")
+ 1)
End If
If _PackageID > 0 AndAlso _connLogDB.State = ConnectionState.Open Then
Dim cmdSQL As OleDbCommand = _connLogDB.CreateCommand()
cmdSQL.CommandText = "Exec dbo.updPackageRun " _
+ "@PackageRunID = " + _PackageID.ToString + "," _
+ "@RunStatus = 'Package Loaded'," _
+ "@SourceID = '" + strSourceID + "'"
Try
cmdSQL.ExecuteNonQuery()
Catch ex As Exception
Dts.Events.FireInformation(0, "LogClass:StartLogging",
ex.Message, "", 0, True)
End Try
End If
End Sub
Public Sub EndLogging(ByVal strEndResult As String, ByVal strSourceID As
String)
Dim endDate As Date = Date.Now
If _PackageID > 0 AndAlso _connLogDB.State = ConnectionState.Open Then
Dim cmdSQL As OleDbCommand = _connLogDB.CreateCommand()
cmdSQL.CommandText = "Exec dbo.updPackageRun " _
+ "@PackageRunID = " + _PackageID.ToString + "," _
+ "@RunStatus = '" + strEndResult + "'," _
+ "@SourceID = '" + strSourceID + "'"
Try
cmdSQL.ExecuteNonQuery()
Catch ex As Exception
Dts.Events.FireInformation(0, "LogClass:EndLogging",
ex.Message, "", 0, True)
End Try
End If
CloseLog()
End Sub
Private Sub OpenLog()
Try
_connLogDB = New OleDbConnection()
_connLogDB.ConnectionString =
Dts.Connections("Logging").ConnectionString
_connLogDB.Open()
Catch ex As Exception
_connLogDB = Nothing
Dts.Events.FireInformation(0, "LogClass:OpenLog", ex.Message,
"", 0, True)
End Try
End Sub
Private Sub CloseLog()
If _connLogDB.State = ConnectionState.Open Then
_connLogDB.Close()
_connLogDB = Nothing
End If
_PackageID = 0
End Sub
End Class
.
- Prev by Date: Microsoft SQL Server DBA's required immediately in Toronto
- Next by Date: How to change dynamically odbc string connection
- Previous by thread: Microsoft SQL Server DBA's required immediately in Toronto
- Next by thread: How to change dynamically odbc string connection
- Index(es):
Relevant Pages
|
