Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II Expand / Collapse
Author
Message
Posted Friday, April 17, 2009 9:24 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II
Post #699930
Posted Monday, May 11, 2009 8:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, April 06, 2014 2:31 PM
Points: 384, Visits: 357
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

Post #714137
Posted Monday, May 11, 2009 8:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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.)
Post #714157
Posted Monday, May 11, 2009 9:57 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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.


  Post Attachments 
example.JPG (21 views, 47.17 KB)
Post #714275
Posted Monday, May 11, 2009 10:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, April 06, 2014 2:31 PM
Points: 384, Visits: 357
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
Post #714345
Posted Monday, May 11, 2009 1:47 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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!
Post #714461
Posted Monday, May 18, 2009 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 1:28 AM
Points: 5, 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?
Post #718993
Posted Monday, May 18, 2009 7:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #719048
Posted Monday, May 18, 2009 7:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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.
Post #719059
Posted Monday, May 18, 2009 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 1:28 AM
Points: 5, 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
Post #719098
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse