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.
View 4 RepliesGreetings,
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?
Thanks,
BCB
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?
Best regards,
Stefoon
Hi,
I have a small requirement in SSIS Error Logging Mechanism.
Presently in my SSIS package i am using a File Connection Manager for creating a Log file.
I have a problem on this regard. Every time when i am executing my DTS package, the error log messages are getting appended to my error log at OS level (say D:error_messg.log). And for this reason whenever my DTS package is getting executed the size of the file is keep on increasing and there by killing my disk space.
I have a requirement for this error logging mechanism. At any time my log file should not exceed more than 20MB.
Or can we remove the log events a week ago or say more than 2 days or say. Just ensuring the log file do not fill up the disk space eventually.
How can we do this?
Any suggestions are greatly appreciated.
Thanks & Regards
Hi All,
As Jamie Thomson has written:
http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
'...we can attach an event handler to the package...and this one event handler will catch all events raised of that event type by every container in the package...'
Fine. Now what I want to do is catch all OnInformation events generated for the package, and disgard all apart from a subset, whose message contains a particular string. So the Q is, how do I/can I interrogate the message for the event that my handler has just caught?
Michael Coles has come up with a neat method of overriding sp_dts_addlogentry, (http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2007/10/09/3012.aspx), but I want to do this within the package, not outside, to avoid the change becoming global to every package writing to the same sysdtslog90.
Hope this is poss.,
I'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.
I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.
I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.
I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.
Can anyone advise?
Hi all,
I have a custom component for which I want to log entries, but I a quite new to SSIS and I do not understand ho to access the resulting logs.
I have overriden like this
public override void RegisterLogEntries()
{
LogEntryInfos.Add("Log entry",
"This is the log entry for the component",
Microsoft.SqlServer.Dts.Runtime.Wrapper.DTSLogEntryFrequency.DTSLEF_CONSISTENT);
}
and I am adding log messages like this
private void DtsLogMessage(string message)
{
DateTime now = DateTime.Now;
byte[] additionalData = null;
m_ComponentMetaData.PostLogMessage("Log entry",
m_ComponentMetaData.Name,
message,
now, now, 0, ref additionalData);
}
However, they do not appear in the log viewer.
Can you guys help ?
Thanks !
I'm trying to implement a custom log table. To keep the discussion simple, let's say I only have 1 column in this table and all I want to write in it are
"Start" when the package starts
"Error" when it encounters an error
"Finish" when the package finishes. Even if there was an error, I still want to enter "Finish'.
My Control Flow has 3 task objects, 2 Execute SQL Tasks, and 1 Data Flow Task in between them.
The first Execute SQL Task does an insert statement for the Start and the second Execute SQL Task does an insert for the Finish.
To capture any package errors, I also have an Execute SQL Task (to insert "Error") in the Event Handler for OnError. I see that when I cause an error in my package it can raise multiple OnError events, which will envoke my Execute SQL Task multiple times. (This is good because it will allow me to write a line per error event with the error description.)
The problem I have is, how do I write the "Finish" log when I have an error? If I put the insert for the finish in the same Execute SQL Task with the errors, then it will write a "Finish" for every error. But I can't put it anywhere else because if I put it anywhere else, the package never makes it there because it stops at the OnError Event Handler.
Or is there a way for me to tell the package to do the 2nd Execute SQL Task all the time?
Lastly, is there a better way to do this kind of custom logging?
I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C: empfile.txt
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows.
SSIS package "Package.dtsx" finished: Success.
I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.
This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?
public override void Cleanup()
{
ComponentMetaData.FireInformation(0, ComponentMetaData.Name,
"Inserted " + m_rowCount.ToString() + " rows into " + m_fileName,
"", 0, ref m_cancel);
base.Cleanup(); // or not
}
In my console application code which creates and executes SSIS package I have this code.
Code Block
provider.ConfigString = loggingConnection.Name;
package.LoggingOptions.SelectedLogProviders.Add(provider);
package.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;
package.LoggingOptions.EventFilter = new string[] { "OnPipelineRowsSent","PackageEnd" };
package.LoggingMode = DTSLoggingMode.Enabled;
I use a custom log component to log the events. However the "PackageEnd" event does not seem to get fired at all.
Am I missing something in this?
Thanks
I would like to fire a pre execution event, grab the name of the stored procedure (source of the sql task), insert a record with the name and datetime, and then fire a post event that would update the record with a modified dated.
What is the best way to capture the source value name in the execute sql task.
Hi:
Just implemented logging on my package, and the type of logging is to a text file. Any ideas on where should i store my log folder/file, so when I do my build and deploy, I end up moving the folder as well. Do I have to manually do that? How would that work? What if I save the log file outside of my project folder? Where is the log folder/file path specified? Thanks.
I am executing the SSIS package and generating the execution log information in SQL server and text file.
i.e. using the SSIS---->logging and there SSIS log provider for SQL server and SSIS log provider for text file.
Now I want to push the execution information of all the events in SQL server whereas in text file I want to log only the OnError events.
Now I am unable to to select the different set of the events for two log provider (SQL server and text file)
Please suggest how to do this.
Logging in SSIS as compared to DTS is more complex to set up with so many events. What events should I choose if I need something similar to DTS package logging i.e. I simply want to see which tasks executed at what time and whether they failed or succeded and if they failed, what was the error?
View 10 Replies View RelatedHi,
I have migrated DTS package into SSIS. One of the ActiveX Script contains the following code. I have a question regarding changing this code.
What is the equilaent object of reading LogFileName in SSIS? I setup the Logging in SSIS, but didn't fidn any option using which i can refer to logfile as it is done in the following DTS code.
It'll be a great help if somebody can assist me in this.
Thanks in advance.
Neetu
Code:
Function Main()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
dim fso
dim pkg
dim filename
dim srcfile, logfile
dim inchar
set fso = CreateObject("Scripting.FileSystemObject")
set pkg = DTSGlobalVariables.parent
set logfile = fso.OpenTextFile(DTSGlobalVariables.parent.Properties("LogFileName").Value, ForAppending, True)
filename = Pkg.Connections("CARSBalance").Properties("DataSource").Value
If (fso.FileExists(filename)) Then
set srcfile = fso.OpenTextFile(filename, ForAppending)
' Add a blank character to the file.
srcfile.Write " "
srcfile.Close
set srcfile = fso.OpenTextFile(filename, ForReading)
'Read a line from the source file
inchar = srcfile.Readline
If Len(inchar) <= 1 then
'Have to close the file, then open ForWriting
srcfile.Close
logfile.WriteLine ("File is empty: " & filename)
logfile.WriteLine ("Adding dummy record")
set srcfile = fso.OpenTextFile(filename, ForWriting)
srcfile.WriteLine(" ")
End If
Main = DTSTaskExecResult_Success
srcfile.Close
Else
logfile.WriteLine
logfile.WriteLine ("$$$$$$$$$$$$$ File does not exist: " + filename + " $$$$$$$$$$$$$")
logfile.WriteLine
SuccessReturncode = -1
Main = DTSTaskExecResult_Failure
End If
logfile.Close
End Function
Hi,
My job is to create an audit report. I have two tables A and B which is linked through a common field Id.
if Id column of A table doesnot find value for Field1 in B for the corresponding Id then an error should be reported as follows:
"Missing record in B"
if Id column of A finds value for Field1 but Field2 doesnot have value then warning should be generated.
These Errors and warnings should be logged in Audit Report file.
Can any one guide me how to do it?
Anyone know if there is a way to make the package only log errors? I only have errors checked, but it still logs the package onstart and onend events. Is there a way to turn this off?
Thanks
What all would need to be done in order to capture a time that an SSIS package ran and completed? I have had a thread about this before here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1310953&SiteID=1
But, I have been busy and have been off and on with this issue. I really need to find some simple way to show in a report using SSRS the "as-of" date when the data was pulled in. Is using the Execute SQL task going to be that method?
Any information is greatly appreciated.
Thanks
Second time around w/this one. I'm running my sqlserveragent with a domain admin account and everything works well. I then switch to a domain user and the package fails. Windows app log contains ths
Package "Package1" failed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
So I go to the sql server agent jobs and select the job I'm trying to run. I then go to Job Step Properties and under the logging tab choose SSIS log provider for Text files. What is supposed to go in the Configuration String?
It will let me type a text file in there or choose from one of my "3" datasources in my package. But since I'm developing this on another machine the choice to log to the file system makes no sense.
Please do not refer me to the kb article (http://support.microsoft.com/?kbid=918760) I've read and re-read that. All that I want to do is find out why the package is failing.
Thanks.
Hi,
What SSIS logging option should I check to log the success of each component? In log file, I'd like to see what component is currently SSIS executing and what component(s) passed the execution.
Thanks
Hello Everyone,
I have written a custom task which writes the details of an error in the windows event log.
I use the on error event to invoke this task.
To simulate the error I try to convert a string into a number. I see that there are multiple as many as 6 messges with different error codes being logged into the windows event log.
They vary from something tangible like number conversion failed to obsure things like thread was being aborted.
Is this normal? to see multiple messages for one type of error.
Can I control this, so that only one message is sent into the windows event log.
(reason is that we have a monitoring software which may raise multiple admin alerts for the same issue).
Thanks for your help in advance.
regards,
Abhishek.
I'm trying to use the information logged by SSIS packages into the database (the sysdtslog90 table) to track errors. Unfortunately, when the errors are written to the log they are associated with a source and sourceid and not the package and package ID. Sometimes the source is the package, but often it is a process within the package. All I need is some way to associate each error with a package--a package name/ID on each row in the log. This seems like it ought to be a pretty basic need, but I can't figure it out.
Your help is greatly appreciated.
devin
When running SSIS packages, the package execution information is logged in the SYSDTSLOG90 table which contains the following columns.
id
event
computer
operator
source
sourceid
executionid
starttime
endtime
datacode
databytes
message
After executing a package, I found that the values in the "executionid" column are the only ones that are unique. Can we use this to determine what package was run? We are trying to architect a solution that would allow us to determine as to how long a package ran, if it ran into warnings / errors etc., We can easily accomplish this by having our own table and using Global variables within packages, we could insert / update this table. Appreciate any help.
Hi,
Can anyone tell me the best way in SSIS to log performance at control flow level i.e. per task I have in my control flow and what performance characteristics it is possible to log.
Thanks in advance
Hi
I want to have logging enabled for my SSIS package and have the logs wriiten to an log in the event viewer.
How can I do it.
Thanks
Sai
I am having a problem where duplicate log statements are being written to a log file (as defined by a log provider).
I believe that this is because in the logging dialog box, I have ticked the checkbox next to a child task to override the logging functionality.
I need to do this because it is a script task and I want to capture "ScriptTaskLogEntry" events (something that I cannot do at the parent level).
However by doing this I seem to get the script events written at the parent, as well as at the Script Task level.
Is there any way of avoiding this, but still capturing the log events from the script task?
Another issue that is possibly linked is that I am getting an error from the log provider:
The SSIS logging provider "SSIS log provider for Text files" failed with error code 0x800700EA ((null)). This indicates a logging error attributable to the specified log provider.
Could this be because of the parent and child task are both attempting to write to the same log provider?
Thanks in advance
Hi. I've tried several things to log some information inside of the ScriptTask. I've tried Console.WriteLine(), Debug.WriteLine(), and Dts.Log().
All compile and work, but my problem is I can't find the output to save my life.
I have logging turned on in my SSIS package, but the data doesn't show up in that file. I've check SQL Server's log. Event Log in OS. All no luck.
Please help.
I am currently using the SSIS Logging feature in my SSIS package. Currently, I have defined a destination log file, and each time the package is executed the log file gets appended with that days log.
Im trying to figure out how best to keep the log file name static (it gets emailed out, and my email client looks for a particular log file name) yet include only todays log information and append the rest of the log information to a history log file or something like that.
Has anyone tried doing something similar, or have any ideas on how best this can be accomplished?
Thanks
I am currently working to write a progress log for my SSIS packages. So far I am able write a new log entry, update this log entry using OnProgress and OnError Event Handlers. I'd like to take it one step further. Whenever the package ends whether cancelled or finished normally; I'd like to write to my logging table COMPLETED_ABNORMALLY on cancelled or COMPLETED_NORMALLY on a normal finish of the package. I'm not sure where to begin with this process. I'd like to utilize a simple method and event handler.
View 0 Replies View RelatedJust like in DTS where we can add error file so that if the DTS package fails we can see what caused the DTS to fail, likewise do we have anything like error logging file in SSIS. I greatly appreciate your help on this. thanks!!
View 1 Replies View RelatedAfter logging is configured in SSIS package, it seems that after each execution the output is appended to the log file (we are talking about log provider for text files in this case). As a result the file just keeps on growing. I would like to overwrite old information with each run, but I can't find where to configure this. Anybody knows?
Thank you!
Hi,
I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.
Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.
Vivek S
Has anyone attempted (with success) to capture the sql command text from SSIS packages at runtime for logging?
What approach was used?
ProjectREAL used a stored proc to execute all sql statements.
This seemed rather poor in design (formulate a string to pass to the sp just to log the sql command text).
This especially seemed problematic as the stored proc would have to be on the source system.
I have been trying to store my sql in variables which were set to eval as expressions and was hoping to use an ExecuteSQL task in the PreExecute event handler for each task, source, transform or destination which I required logging of the sql command text.
Problem is that, depending on how the expression is formulated (with coding parameter markers or replacing parameter's markers with values) I might only get the pre-parameter replacement version of the sql command text rather than the final parameter replaced sql command text. Also not sure that this design would work with destinations.
Any tricks of the trade to share?