Turning DTS Packaing logging ON/OFF 
Author Message
 Turning DTS Packaing logging ON/OFF

Hi all

I have a number of DTS package running on SQL2K/Win2K and I want to
dynamically turn the package logging on or off depending on a global
variable.

Here's what I've got so far,

Option Explicit
Function Main()
        On Error Resume Next
        Dim oWSh ' Windows Shell Script object
        Dim oPkg ' DTS Package object
        Dim sPkgName ' DTS Package Name
        Dim oConn ' ADO connection object
        Dim oRS ' ADO recordset
        Dim sConn ' ADO connection string
        Dim sSQL ' SQL string to execute
        Dim sMsg ' Err message
        ' open windows script object so we can log message to event log
        Set oWSh = CreateObject("WScript.Shell")
        ' setup ADO connection and recordset objects
        Set oConn = CreateObject("ADODB.Connection")
        Set oRS = CreateObject("ADODB.Recordset")
        ' open ADO connection using stored connection string
        sConn = DTSGlobalVariables("sConn").Value
        oConn.Open sConn
        ' clear error message global variables
        DTSGlobalVariables("sErrCode").Value = "Empty"
        DTSGlobalVariables("sErrTxt").Value = "Empty"
        ' open DTS package object so we can get the package details
        Set oPkg = DTSGlobalVariables.Parent
        sPkgName = oPkg.Name
        ' check if we need to log package execution
        If DTSGlobalVariables("iLog").Value = 1 Then
                ' build SQL statement to return properties for package logging  

                ' open the resultset
                oRS.Open sSQL, oConn
                ' set package logging properties
                If Err.Number = 0 And oRS.EOF = False Then
                        ' assign results from query to package properties
                        oPkg.LogToSQLServer = DTSGlobalVariables("iLog").Value
                        oPkg.LogServerName = oRS.Fields(0)
                        oPkg.LogServerUserName = oRS.Fields(2)
                        oPkg.LogServerPassword = oRS.Fields(3)
                        ' set connection to use Windows or SQL security
                        If oRS.Fields(4) = 1 Then
                                ' set logging to use Windows security
                                oPkg.LogServerFlags = 256
                        Else
                                ' set logging to use SQL security
                                oPkg.LogServerFlags = 0
                        End If
                Else
                        sMsg = "ERROR: " & sPkgName & " failed. Could not "
                        sMsg = sMsg & "assign properties for the Package "
                        sMsg = sMsg & "Logging SQL Server connection."
                        DTSGlobalVariables("sErrCode").Value=vbObjectError+1100
                        DTSGlobalVariables("sErrTxt").Value = sMsg
                        oWSh.LogEvent 1, sMsg
                        Err.Clear
                End if
                oRS.Close
        Else
                ' turn off package logging
                oPkg.LogToSQLServer = DTSGlobalVariables("iLog").Value
        End If

        ' check if error occurred
        If (DTSGlobalVariables("sErrCode").Value <> "Empty") Then
                ' write message string to event log
                oWSh.LogEvent 1, sMsg
                Main = DTSTaskExecResult_Failure
        Else
                Main = DTSTaskExecResult_Success
        End if
        Set oConn = Nothing
        Set oPkgConn = Nothing
        Set oPkg = Nothing
End Function

The first step in the package builds the ADO connection string that is
assigned to the sConn Global variable. The above script currently
executes as the second step in the package and runs without problems.
In subsequent ActiveX Script tasks I can bring up the same
"LogToServer" properties and see that they are set correctly. However,
nothing is logged in the sysdtspackagelog and sysdtssteplog tables! I
tried putting both scripts in the first step, but that doesn't make
any difference.

Any ideas at would be most helpful.

Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface



Fri, 09 Sep 2005 15:34:35 GMT
 Turning DTS Packaing logging ON/OFF



Quote:
>Hi all

>I have a number of DTS package running on SQL2K/Win2K and I want to
>dynamically turn the package logging on or off depending on a global
>variable.

Phil,
I have looked at a similar problem  myself, and I don't think it can be
done. The crux of the problem I believe is that you are already running
the package, and you cannot change logging options after a package has
started. In theory it has already logged the start event for this first
task where you are then trying to change it all, so you could say all
the logging connections are already "in memory", and hence beyond your
reach, or at least for this execution. If you where to save the package
post execution it would probably take, but for this execution you are
too late.

Only way round this is to execute the package yourself through code, and
set the logging options prior to calling the Execute method.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com



Sat, 10 Sep 2005 08:32:13 GMT
 Turning DTS Packaing logging ON/OFF
Darren

Thanks for your reply. I had come to the same conclusion regarding the
connection already being established, just wanted some confirmation.

It's interesting how the properties can be set and queried. If the
properties are effectively read-only, you probably shouldn't be able
to modify them. Oh well, just another DTS quirk. Roll on the next
version.

Phill

Quote:



> >Hi all

> >I have a number of DTS package running on SQL2K/Win2K and I want to
> >dynamically turn the package logging on or off depending on a global
> >variable.

> Phil,
> I have looked at a similar problem  myself, and I don't think it can be
> done. The crux of the problem I believe is that you are already running
> the package, and you cannot change logging options after a package has
> started. In theory it has already logged the start event for this first
> task where you are then trying to change it all, so you could say all
> the logging connections are already "in memory", and hence beyond your
> reach, or at least for this execution. If you where to save the package
> post execution it would probably take, but for this execution you are
> too late.

> Only way round this is to execute the package yourself through code, and
> set the logging options prior to calling the Execute method.



Sun, 11 Sep 2005 06:07:51 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. how to turn off transanction log while data import from ACCESS(DTS)

2. Can Transaction log be turned off?

3. Turn off logging

4. Turn off Logging of TEXT columns ?

5. Turn off logging?

6. Turning off the log file

7. Q: Turn off the transaction-log?

8. Help! Turn Off Log

9. How do I turn off transaction logging

10. Turn Off Logging for Alter Table Statement

11. turn off logging?

12. how to turn transaction log off?


 
Powered by phpBB® Forum Software