SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II


Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II

Author
Message
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2090
Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II
nancy.lytle
nancy.lytle
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 698
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
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2090
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.)
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2090
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.
Attachments
example.JPG (31 views, 47.00 KB)
nancy.lytle
nancy.lytle
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 698
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
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2090
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!
Mik-BI
Mik-BI
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 50
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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62945 Visits: 19111
Good job on the series, David!

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2090
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.
Mik-BI
Mik-BI
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 50
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search