April 17, 2009 at 9:24 pm
Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II
May 11, 2009 at 8:01 am
I am having trouble getting the SSIS to work. I should mention I am using SS 2005 and VS 2005 and that I am a novice as SSIS and XML.
I created the Control FLow objects, Get XML from Database and the Transform XML with XSL into HTTP. What I am having problems with is the Transform object, First issue is what XML files to place where in the Transform task. I have set the input as the transform.xsl and the output as the files where I want the info to go and the Second Operand as the saved result (saved as DBTables.xml) from the SQL script which created the XML for the tables in the database. However when I try and run the package I get the following error:
[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".
So I don't know if I have the files in the wrong places or what. Plus when I tried to validate the XML file generated from the script it has the error:
Missing document type Declaration [DTD]
Any assistance would be greatly appreciated.
Nancy
May 11, 2009 at 8:16 am
Hi Nancy,
I'll try to check this out this evening and send you some feedback. (It's afternoon here.)
Regards,
David.
(Alternatively checkout MSXSL from the Microsoft downloads pages.)
May 11, 2009 at 9:57 am
Nancy,
It looks to me that you've not set the OperationType correctly in the XML task. (Your doing XSD validation instead of XSLT.)
I've attached a screenshot of how the xml task should look.
I created an SSIS package with just one task, and configured it as per the screenshot.
You should save the xml (from the query) and the XSL from the article, and then point to them in the task. (You'll have to create new text file connections.)
Make sure the SaveOperationResult is set to true and correctly configured to.
(Just follow the attached screenshot.)
Let me know how it goes,
David.
May 11, 2009 at 10:58 am
Looks like I got it to work and oh goodness, it looks like it is going to be fun to play with!
Thanks for everything,
Nancy
May 11, 2009 at 1:47 pm
nancy.lytle (5/11/2009)
Looks like I got it to work and oh goodness, it looks like it is going to be fun to play with!Thanks for everything,
Nancy
excellent! enjoy!
May 18, 2009 at 6:37 am
A great concept, excellently packed and ready for use out-of-the-box.
Reading the article sparked off an idea but, as I'm relatively inexperienced in working with SQL server & XML, I'm not sure if this would be possible. Anyway here is the idea.
In applications that I have worked on there has been a need, especially in the area of master data (codes, descriptions, customers account information etc.), to record who changed what data and when. Something that can take as much effort to do as to maintain the information itself.
Extending the data collected to include the who & the when will allow the rollback, rollforward of data on a user basis and solve the business auditing need in one and the same solution.
I'm assuming that the system tim & the logged on user (not necessarily the db user) are available as either variables or function calls.
To me, this almost seems too good to be true. There must be a catch somewhere or what?
May 18, 2009 at 7:34 am
Good job on the series, David!
May 18, 2009 at 7:44 am
mcl (5/18/2009)
A great concept, excellently packed and ready for use out-of-the-box.Reading the article sparked off an idea but, as I'm relatively inexperienced in working with SQL server & XML, I'm not sure if this would be possible. Anyway here is the idea.
In applications that I have worked on there has been a need, especially in the area of master data (codes, descriptions, customers account information etc.), to record who changed what data and when. Something that can take as much effort to do as to maintain the information itself.
Extending the data collected to include the who & the when will allow the rollback, rollforward of data on a user basis and solve the business auditing need in one and the same solution.
I'm assuming that the system tim & the logged on user (not necessarily the db user) are available as either variables or function calls.
To me, this almost seems too good to be true. There must be a catch somewhere or what?
To include the Who and the When is trivial - you can just add columns with default values to the audit table. (e.g. getdate() and suser_sname).
What I provided is the core of the solution - and while it does quite a bit 'out of the box' you'll still have to adapt / interpret and apply in order to get it working for your situation.
Is there a catch? Yes, probably - although in certain circumstances it might be just what you're looking for. The basic 'issues' are principally (1) that the trigger is quite chunky, and so may take as much time / resources as your operation (2) Everything here is auditing to the same table so there may be competition to insert rows.
Essentially you've got to consider the possibility that the auditing may bring your database to a halt, if you're not careful. But either for specific tables or for a database with few updates, there may be no problem at all.
Regards,
David.
May 18, 2009 at 8:14 am
David McKinney (5/18/2009)
To include the Who and the When is trivial - you can just add columns with default values to the audit table. (e.g. getdate() and suser_sname).What I provided is the core of the solution - and while it does quite a bit 'out of the box' you'll still have to adapt / interpret and apply in order to get it working for your situation.
Is there a catch? Yes, probably - although in certain circumstances it might be just what you're looking for. The basic 'issues' are principally (1) that the trigger is quite chunky, and so may take as much time / resources as your operation (2) Everything here is auditing to the same table so there may be competition to insert rows.
Essentially you've got to consider the possibility that the auditing may bring your database to a halt, if you're not careful. But either for specific tables or for a database with few updates, there may be no problem at all.
Regards,
David.
I've just noted that I didn't sign my comment, please accept my apologies.
As with the catches, I'd already got my solution orientated blinkers on. You're absolutely right it would potentially require lots of hardware to fully audit an OLTP system database, I was looking at the solution for low change Master Data tables, which should keep database resource use and contention within the bounds of acceptability.
Best regards
Mik
August 6, 2009 at 4:47 am
Very cool! Rated five stars - I particularly like the effort made with the article 's presentation, the structure of the SQL (CTEs and nested FOR XML are a happy combination), and the XML Spy diagram. Excellent stuff!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 6, 2009 at 5:53 am
Thanks, Paul - High praise indeed! When used appropriately XML certainly has it's place in the SQL world. I'm sure you'll agree 😉
August 6, 2009 at 7:13 am
//EDIT -- Nevermind, apparently if you actually pay attention to the steps everything works just find. Sorry about that.
I can't figure out what I have done wrong with the SSIS package. I am getting this error on the execute XML task:
[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".
Here are my values:
OperationType = XSLT
SourceType = File Connection
Source = transform.xsl
SaveOperationResult = TRUE
DestinationType = Fil Connection
OverwriteDestination = TRUE
SecondOperandType = Variable
SecondOperand = User::XmlAsString
Any clue on what I need to change?
Thanks
August 6, 2009 at 11:20 am
I did run into one problem with the trigger scripts. Since the quotename function is used the argument is of type sysname which is nvarchar(128). If you have a field longer than this it nulls out the entire field and since the bigAudit table does not allow any null values in any column the trigger fails. I have changed the 1 trigger that I ran into by just including the quotes myself. So for any column that might have data longer than 128 I changed the insert/delete/update sections of the trigger from:
QUOTENAME(i.[Column Name],'''')
to
(''''+i.[Column Name]+'''')
Before making this change I would get this error:
Msg 515, Level 16, State 2, Procedure trgAuditscripts, Line 35
Cannot insert the value NULL into column 'RollForwardSQL', table 'Audit.dbo.BigAudit'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Just wanted to post this in case anyone else was having problems.
August 7, 2009 at 12:31 am
Good find, Matt!
I completely forgot / overlooked that quotename takes a sysname parameter.
I used it principally to take care of strings which already have a quote in them. Quotename will double them up, if you know what I mean. o'leary will become 'o''leary'. I guess I can just use the replace function, or write a custom quotename function which is less restrictive than the original!
Thanks a lot for sharing your find with us. I hope you have fun with your auditing!
Regards,
David.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply