Audit table

Question

Audit table

sqlbaby2

Ten Centuries

Points: 1351
More actions
August 8, 2017 at 2:37 am

#327850

Hello expert,
I want to keep track how many records that has been inserted into destination table as well as other info like table name, status and create date.
Example below, once data inserted into dest table, I would like to have an mechanism which can insert required audit data into an audit table. The audit table should has info like Package Name, Destination Table Name, Number of Records been inserted, Status, Create date.
I need an idea how to implement this mechanism. Any suggestions are highly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

I notice this post is 2 weeks old and you never got an answer. I don't even know how to spell "SSIS" but I do think that this is a fundamental question, the answer to which would be incredibly useful to everyone that uses "SSIS". So I'm "bumping" this post for you.

Also, if you ever did come up with an answer, would you mind terribly if you posted it despite the fact that no one was able to figure it out on this forum so far?

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

the problem is this isn't so straightforward in SSIS as you might want it to be. If you use the Logging features of SSIS, you can record this info into a text file or sysssislog table of a database you choose. The data in those logs may be more verbose than you wish. A small run just logging OnInformation of the Data Flow Task for one table load, would produce several rows, each row having times, component name, etc:
PackageStart Package_Name
OnInformation (nonsense about validation and preparing for a few lines)
...
OnInformation "component "OLE DB Destination" (39)" wrote 208 rows.
PackageEnd Package_Name

If you want it to be simpler, unfortunately it means more work like adding a Row Count item in your Data Flow, then have another step save the results of the variable to your simpler log table, but then if any of the rows error in the destination this Row Count would be different than the actual rows in the table.
http://www.techbrothersit.com/2013/07/ssis-how-to-use-row-count.html

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Chris Harshman - Tuesday, August 22, 2017 1:19 PM
the problem is this isn't so straightforward in SSIS as you might want it to be. If you use the Logging features of SSIS, you can record this info into a text file or sysssislog table of a database you choose. The data in those logs may be more verbose than you wish. A small run just logging OnInformation of the Data Flow Task for one table load, would produce several rows, each row having times, component name, etc:
PackageStart Package_Name
OnInformation (nonsense about validation and preparing for a few lines)
...
OnInformation "component "OLE DB Destination" (39)" wrote 208 rows.
PackageEnd Package_Name
If you want it to be simpler, unfortunately it means more work like adding a Row Count item in your Data Flow, then have another step save the results of the variable to your simpler log table, but then if any of the rows error in the destination this Row Count would be different than the actual rows in the table.
http://www.techbrothersit.com/2013/07/ssis-how-to-use-row-count.html

So my initial reaction of "Why in the hell would you want to use SSIS for this?" wasn't just a phobic reaction after all. 😀 Thanks, Chris.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Enabling Package logging is the simplest way to capture the information with no custom engineering involved to capture the data. Selectively turning on certain logging events for a Data Flow Task will give you row counts for that component in the log.

Retrieving data from the logging table will be up to you in terms of how you want to filter out the noise and display the data.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

David Burrows SSC Guru Points: 65138 More actions · Answer 5

It may be possible using Multicast, Aggregate and Derived Column transformations

Far away is close at hand in the images of elsewhere.
Anon.