Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I want to transfer my audit data from an audit file to a table using SSIS in SQL Server 2012. I don`t have experience with SSIS, so I am asking for your help.
I have already created a table to store the audit data. I am choosing OLE DB Source as source, but what should be the destination ? There is Audit option in "Other Transforms", but how can I use it ?
The Audit Transformation in SSIS Data Flow 'Other Transforms' section is really just for adding data about the environment in which the package is running to the data flow, things like the package name, package id, computer name etc.
If you want to work with SQL Server Audit data that has been output to a file, you probably want an OLE DB Source running SQL similar to this, and some kind of management around which audit files you've already imported:
SELECT *
FROM fn_get_audit_file ( 'S:\SQLAudit\auditTableAccess_*.sqlaudit', DEFAULT, DEFAULT );
This article covers that process quite well: https://www.mssqltips.com/sqlservertip/3444/automate-the-import-of-sql-server-audit-files-into-sql-server-using-powershell/
