SQL Server Storage Engine & Tools (SSQA.net)

SQL Server Auditing and Logging - SSIS Audit Transformation comes handy for you in database platform

If you work in Financial based Enterprise then Sarbanes-Oxley is not a new concept for you as per American law that regulates the accountability of publicly owned companies. Just to talk about the main purpose of the law it to make sure the financial statement that a company reports is legitimate and that there’s no room for someone to manipulate it. 

DBAs will see this law as Data Audit information management, in terms of capture the data and present as and when they are queried. The Audit transformation needs to managed efficiently using SQL Server tools & engine capability, which gets you with SQL Server Integration Services (SSIS).  It is evident that managing Audit transformation adds extra columns to the data flow that contains lineage data about the package, task or system. So how do you go about obtain the relevant values for data transformation audit, when you create a package in Business Intelligence Development Studio (BIDS) by using the graphical interface that SQL Server 2005 Integration Services (SSIS) provides, you set the properties of the package object in the Properties window.  

Here is the list of columns you can use to obtain complete AUDIT information from SSIS packages (source;Technet)

Using a SSIS package properties you could transform this Audit information to the data flow. This transformation would most frequently be used to capture lineage data of records that SSIS had to manipulate or that it couldn’t load. For example, for rows containing errors that couldn’t be corrected automatically, you may wish to add auditing data to the row before inserting it into an error queue as shown below.

Error outputs easily solve this problem by allowing you to specify how you’d like SSIS to handle the error. You can choose to ignore the error, fail on the error or redirect it to a different path in the pipeline. The advantage to redirecting the row is you can attempt to cleanse the row, and, if that doesn’t work, direct it to an error queue to be manually corrected the next morning.

To configure a transform or connection to output records that have failed to a different path, you can click on Configure Error Output in most transforms or click the Error Output page in a Source or Destination. Once you have the dialog box open (shown below), specify how you’d like to handle the error. There are two different classes of errors: truncation errors and transform or connection specific errors. A truncation error occurs when you try to move more data into a column than will fit. A transform or connection specific error will vary based on the transform or connection you’re using. A source and destination might have conversion errors, while the transform might have errors specific to the transform. For example, a Lookup transform will have an error if the record could not be found in the lookup operation. The type of error can be seen in the right Description column below.