Logging All SSIS SQL Statements For Audit
Nov 16, 2007
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?
View 1 Replies
ADVERTISEMENT
May 4, 2015
I ma having a package having 10 DFT Task.In Each DFT Task there are odd number of insert and update at the end
for example.
source>lookup>lookup1>lookup2>transformation>conditionalsplit>
1 New CurrYearMinus4 YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 4) && ISNULL(Run_Id)
2 New CurrYearMinus3 YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 3) && ISNULL(Run_Id)
3 New CurrYearMinus2 YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 2) && ISNULL(Run_Id)
4 New CurrYearMinus1 YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 1) && ISNULL(Run_Id)
[code]....
View 3 Replies
View Related
Sep 21, 2007
Hi
I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.
Any help will be greatly appreciated!
Thanks,
Alla
CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @StatusMsg varchar
declare @ServerTraceFile varchar
set @ServerTraceFile = 'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info'
set @maxfilesize = 1024
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec @rc = sp_trace_create @TraceID OUTPUT, 0, N'\hostnamedbauditlogmy_dir', @maxfilesize, NULL
print @TraceID
if (@rc != 0) goto error
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
--SELECT @StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @TraceID
-- display trace id for future references
select TraceID=@TraceID
goto noCursor
error:
select ErrorCode=@rc
noCursor:
return
GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GO
View 3 Replies
View Related
Jun 30, 2015
1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process.
2. Information like WHO and WHEN printed the report and WHAT data was viewed?
View 5 Replies
View Related
Jun 5, 2015
In my package there are 10 DFT.
Each DFT have source > Tranformation > Conditionsplit > Rowcount_Transformation > Oledb Command
> Rowcount_Transformation1 > Oledb Command1
> Rowcount_Transformation2 > Oledb Command2
> Rowcount_Transformation3 > Oledb Command3
All update hapend on diffrent Table.I want to log in Audit table .
My audit table like
Table_Name Insert_count Update_count
How can I log the package having multiple OLEDB Destination.
View 7 Replies
View Related
Oct 25, 2004
Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?
They also want us to track schema changes. Is this possible?
Thanks, Dave
View 5 Replies
View Related
May 14, 2014
I have a tables that looks like this:
TABLE1
COL1
COL2
LOG_TABLE
TABLE_NAME
USER_ID
SQL_STATEMENT
DATETIME
I need a trigger (I assume) to log any updates/inserts/truncate/deletes/etc. to any changes as well as their USER_ID that has been executed to TABLE1.
View 1 Replies
View Related
Aug 31, 2015
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
View 9 Replies
View Related
Sep 12, 2005
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 Replies
View Related
Nov 1, 2014
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
View 1 Replies
View Related
Jan 24, 2007
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.
View 3 Replies
View Related
Oct 18, 2006
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.
View 1 Replies
View Related
Sep 20, 2006
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 Related
Dec 15, 2006
Hi,
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
View 2 Replies
View Related
Jun 23, 2006
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?
View 5 Replies
View Related
Jan 26, 2006
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
View 2 Replies
View Related
May 1, 2007
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
View 10 Replies
View Related
Jun 1, 2006
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.
View 12 Replies
View Related
Mar 26, 2008
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
View 2 Replies
View Related
Apr 24, 2007
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.
View 3 Replies
View Related
Jun 6, 2007
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
View 3 Replies
View Related
Apr 3, 2008
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.
View 6 Replies
View Related
Nov 17, 2006
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
View 1 Replies
View Related
Dec 19, 2007
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
View 1 Replies
View Related
Oct 12, 2007
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
View 5 Replies
View Related
Oct 11, 2006
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.
View 5 Replies
View Related
Mar 27, 2007
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
View 23 Replies
View Related
Apr 24, 2015
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 Related
Jun 6, 2008
Just 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 Related
Jul 28, 2006
After 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!
View 4 Replies
View Related
Jun 18, 2007
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
View 10 Replies
View Related
Nov 29, 2007
We have a package that has a connection called Load_DimItem.trc. We don't need this logging enabled for this package anymore. However, if I delete the connection, and delete the log provider (SSIS log provider for SQL profiler), I get errors when trying to close the package after debugging. I get: "Cannot detach from one or more processes. [3172] The object invoked has disconnected from its clients."
How can I get rid of this error?
View 7 Replies
View Related
Jan 18, 2006
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies
View Related
