I have a DTS package that performs some custom logging of othe rchild SSIS
packages. Everything is being logged as it should be, except for the
PACAKGESTART and PACKAGEEND events.
The pacakage executes a series of child packages, but even the main - or
parent - package doesn't log the PACKAGESTART and PACKAGEEND events.
I've attached the script for review:
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
' 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() {"PackageStart",
"PackageEnd", "OnProgress", "OnPreExecute", "OnPostExecute", "OnError",
"OnWarning", "OnInformation", "Diagnostic"}
' Set each task in package to Log
Dim evProvider As EventsProvider
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
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 "PackageStart", "PackageEnd"
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID, dataCode)
Case "OnError", "OnWarning", "Diagnostic"
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID, dataCode)
Case "OnInformation"
If messageText.IndexOf(" wrote ",
StringComparison.CurrentCultureIgnoreCase) >= 0 _
AndAlso messageText.IndexOf(" rows",
StringComparison.CurrentCultureIgnoreCase) >= 0 Then
LogStep(logEntryName, sourceName, startTime,
messageText, sourceID, dataCode)
End If
End Select
End If
MyBase.Log(logEntryName, computerName, operatorName, sourceName,
sourceID, executionID, messageText, startTime, endTime, dataCode, dataBytes)
End Sub
I am a SSIS newbie and I have a created a SSIS package that runs every 5 minutes that imports a CSV file. It logs the PackageStart and PackageEnd events to a log file. I would like not to have these events logged. I only want error and warning messages to be logged in order to keep the log file small. With the GUI logging parameters, I was able to get the appropriate error and warning messages logged however, I was not able to turn of the ParkageStart and End messages. The reason to turn off these messages is to keep the log file small.
I have searched through this forum and it appears some sort of custome logger has a bi-product (feature/bug) of not logging these events. According to the threads, this is a bug and will be fixed at some point so this does not appear to long term the solution.
If I cannot turn off these messages, the other option would be to archive the log file every month and keep the current month and the previous months log file. Any log files prior to that can be deleted. Let me know if there are any examples of this. Also, I believe that this package would probably have to be called outside of the SSIS package the imports the CSV file since the import package probably has some sort of file handle open to the log file. Is this correct?
Just finishing of a large ETL system and have aquestion about the following:-
We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?
My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something
This is a repeat listing - third time - of this problem.
Here's the deal: If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.
When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.
In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.
If this is the case, can someone explain why they aren't getting logged?
I've seen other people have run across the same issue...
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Hi, I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes. I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
I have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields: Table_Changed Table_Primary_Key Old_Field_Value New_Field_Value User Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what. I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert. I believe it's possible to do the above (despite my lack of knowledge :) - Does anyone know where I can get more information on how to accomplish the above - or where to start looking? Thanks to any who can guide me in the right direction.
Hi,I´m currently playing around with ASP.NET.Is there a way to log all Queries that are send to the SQL-Server? Something like the query.log of a Mysql.
I have a problem acessing MSDE. My server runs at "NT AUTHORITY/NETWORK SERVICE" so it is not allowed to connect through the windows authentication feature. It seems that the password for the "sa" user account was changed during setup, after reading the logs from the setup I can see that it was changed but I cannot see what it was changed to. How would I set up a new account that I could use to access the server though SQL Authentication???
Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to change DB1 into DB2. This means recording both what happened in the GUI and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log somewhere. Is there a way to set this to output all the changes made from a set date on a database into a SQL log file?
Other than doing bulk insert is there a way to insert in SQL 7 without logging the transaction in order to speed up the process? If so what command(s) allow this?
If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions: 1. Could this happen? 2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Hello everybody. 1. I have 28 Gig database with 4 tables above 4 Gig each with very bad fragmenataion, each table has between 3 and 5 indexes 2. Database set for full recovery and I use custom log Shipping to restore db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table . following took place 1. It took 4 hrs to complite DBCC INDEXDEFRAG 2. log shipping fail. 3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8 hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database when it set to fully recovery and log shipping
I would like to update a SQL 6.5 table with modifications to certain columns, but I don't want any of it logged. Is there a way to do this? Any help is appreciated.
I am getting the following error when I log into SQL server using MS SQL Server Management Studio.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)".
I tried to allow remote connections using Surface Area Configuration, but it is not allowing me to do so.
I am not sure what I need to do. Anybody, please advice.
One hack of a way to do it is to run code similar to that which populates the blk column in sp_who; on a job every 5 seconds. It would store the results of code similar to the sp_who output whenever any of the rows has a value greater than zero in the blk column.
This way, I can see what was going on at the time there was a problem. Does anyone have a better strategy for this?
Hi Guys hope someone can help with this one, I am working on SQL 2000 and having problem with logging a new server instance to be run in query analyzer let me explain,
I have the default sql server and then also an instance called sqlone on the same machine for study reasons.
However after loading the new instance on and then registering it in enterprise manager it appears fine on there, but I am having problem only in query analyzer the sqlone instance does not appear in the drop down menu so that I can select too work on that server
Only reason I need this is as I am practicing adding linked servers and without the second instance being visible it won't work correctly.
For other infomation the instance ok in the enterprise manager, server manager and then in the computer management in the administrative tools in the control panel.
I'm using the Execute SQL Task to import error messages into a table. I'm using the following:
"INSERT INTO SSISLog (EventDescription) VALUES (" +@[System::ErrorDescription]+")"
I've got an import task with deliberate errors in the file to be imported to test the error logging task. When I execute it, I get the following messages:
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". )" failed with the following error: "The name 'An' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (There was an error with input column "IssueNo" (94) on input "Destination Input" (68). The column status returned was: "The value could not be converted because of a potential loss of data.". )" failed with the following error: "The name 'There' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The "input "Destination Input" (68)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (68)" specifies failure on error. An error occurred on the specified object of the specified component. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The ProcessInput method on component "Destination - tbl_clients" (55) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (Thread "WorkThread0" has exited with error code 0xC0209029. )" failed with the following error: "The name 'Thread' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
All, I am using SSIS in sqlserver. I wanted to enable logging in SSIS. I just want to log all the activities in the log file. How can i enable logging in SSIS? I would appreicate if any one could answer for this. thanks in Advance.
Scenario:-I'm brand new to SQL Server 2000-I have a vendor supplied application running on SQLServer 2k.-I need to log which of my users has seen which data (from aparticular table). It's a healthcare privacy thing.-The application does not do this and the vendor has no interest inimplementing it, though it seems pretty easy if one has the app code.So I don't control the application or its code but I need to catchwho's seen what. Note that I don't have to catch it all. In otherwords, I don't need to catch every instance, only whether user X hasseen patient Y's data at all. So I'm talking about a fairly small dataset. The end result I'm heading toward is a case in which a patientdemands to know who's seen his health records. I go to a db table andsay "select username from <viewlogtable> where patient_id = XXX".How do I do that? It'd be easy if I could it were possible to write aselect trigger, or if I had some hooks into the app, but as it is I amstumped. This is trickier than logging updates and inserts...Thanks,John
Hi,I am doing some resource hungry tasks (some extraction and loadingthrough DTS), for which each time the SQL Server Log files gets filledup!Is there any way to stop the logging (like as during restore)?Thanks in advance.-surajit
In SQLServer 2000, I am sure there was a way that you configure thedatabase to log to a file each query that gets run against it.However, I cannot seen to find where to set this up in EnterpriseManager.