sql serversql-server-2012ssis-2012
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 ?
If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?
In the OLEDB connection manager there are various options to load data. If
table or view using fast-load options is selected, then you have to explicitly specify FIRE_TRIGGERS in the advance editor as shown below :
If table or view data access mode is chosen then your triggers should fire normally because the insert is done row-by-row.
There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally but I am sure there is a blog that talks about it. This one might help. http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/
Best Answer
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:
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/