Errors during reading of Audit log files

Apr 24 at 8:20 PM
I am trying to implement the SQL Audit reporting and am running into errors.

I am running it in on SQL 2014, but have upgraded the package. I am getting issues on the load log data flow. The issue appears to be with the read audit log file step. I had to modify the function aud.fn_auditfileget in order to work correctly. That is working fine when I run manually by itself with a filename passed in. I cannot figure out how the package is calling this command and what filenames and parameter it is passing.

My troubleshooting has come to a max and I cannot figure it out.

Error I am getting:
[read audit log file [662]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "The specified values for initial_file_name and audit_record_offset do not represent a valid location within the audit file set. Verify the file name and offset location, and then rerun the command.".
Aug 26 at 8:02 PM
I am running into the same error and in SQL 2014. Did you find a workaround or resolution to this error?
Aug 26 at 8:04 PM
No, I was not able to get past the error and have not had success or time to revisit.
Aug 26 at 10:52 PM
I updated the code for fn_AuditFileGet as below and able to run it. (When offset is 0, I basically pass NULL for the parameter values, so it reads the whole file)
from sys.fn_get_audit_file(@filename, case when @fileoffset = 0 then null else @filename end, case when @fileoffset = 0 then null else @fileoffset end) aud
Oct 13 at 8:49 AM
Edited Oct 13 at 9:23 AM
I'm having a same error as gbargsle. I tried the modification suggested by Avran and now I have the following error:

The external columns for component "OLE DB Destination" (5829) are out of synchronization with the data source columns. The external column "file_name" needs to be updated.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E5D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E5D Description: "Parameter name is unrecognized.".

This is happenning on component "audited action" :

component "audited action" (2926) failed the pre-execute phase and returned error code 0xC020824F.


I'm using the package version LoadLogsPackage_v2. Did anybody succedded to execute the package without errors?
Oct 13 at 7:05 PM
I have not got a chance to try the changes yet. I downloaded the new packages but have not gotten back to this project.
Oct 21 at 1:16 PM
It works like a charm! The error I had initially was related to .audit files: they were wrong for some reason and generated it. I modified the FN according to Avran and everything is fine. I also have integrated the changes in DB from v2 scripts. Even I was able to execute the package on the files residing in the network share - note that you need to use the FQDN server name on UNC path.