Greetings SSIS friends,
Do any of you guys have a link to a website with an example of how to create a solid audit system for SSIS packages
Thanks for your suggestions in advance.
SSIS Auditing example
Hot Topic
- Example of Stored proc conflict resolver?
- Getting the average results based on time.
- format issue
- Using Stored Procedures
- Udding Users and Groups
- ArgumentNullException thrown after second connection
- Do I need multiple versions of Northwind?
- Copying tables using SSIS package
- Service Broker Erro but everything working
- OPENROWSET Error
SQL Server
- Grand Total Needed
- Conversation LIFETIME
- can't access SQL Server 2005 Cubes, when Integrity firewall runs
- Reminder Date
- Replication to custom OLE DB Provider
- drawback when I try to execute SSIS package
- Nonemptycrossjoin alternative
- TSQL Logic Help
- Reporting Services (SSRS)
- how to use the OLEDB command to call a oracle function?
SSIS Auditing example
Barnaby
Hi Darren,
Excuse my ignorance but where do I get hold of this project REAL stuff
developer810
vtortola
WATCH Joy Mundy's Web cast. She talks about it extensively and shows examples of exactly what you need.
Ian1971
Hi guys,
To be honest with you, having read the white paper and various other articles on the web regarding data quality checks and package auditing, I find myself more confused than ever.
I wouldn’t even know where to begin or how many tables I need for my auditing.
I will attempt to explain to you what my requirements are but please don't get me wrong. I am not after a spoon feeding session or anything but a general discussions with some ideas based on your own experiences.
My SSIS package uses 3 data sources which populate a dimension table :
My logging requirements are :
1) Time the package is run.
2) ID of the package run.
3) number of records in the dimension table BEFORE the package is run.
4) number of records in the dimension table AFTER the package is run.
5) Erroneous records to be dumped in a table on the database for future analysis/processing
There are some more but these are the main ones to start the discussion as to what model of auditing I should follow etc.
Hope I'm not being greedy with my requests.
DyngoMan
As a home-brew solution, you could get this functionality by creating a table(s) to hold this information; then for every table have at least 2 procedures; one to insert a row at the begging of the event you want to log and a second one to update the just inserted row with the end results after the event being logged finish. Then in your packages you use Execute SQL task to call those procedures. Before starting something like this, it could be smarter to check what is available out of the box; like event handlers; System variables, package logging. Just a couple of ideas…
Quilnux
Yes, i was talking about that documet. 5 level of tables may be too much; but that depends of your auditing needs. Providing more details of your particular audit needs may make easier for people to help you.
In my experience, so far,logging execution details at the whole batch level (let say a collection of packages that run togheter), then down to the indiviudal package level and finally at the target table level have been enough to cover most of our auditng needs. So on that, I am talking about 3 level of tables.
gudel
rykk
Hi Rafael,
Are you referring to the SQL Server 2005 Data Quality Solutions white paper I already have this and I have also read the section on Auditing but the document is much more high level and I was hoping for a more detailed session on this forum.
The white paper mentions creating a schema consisting of perhaps 5 tables (which seem too much for my liking) but perhaps a detailed discussion here might convince me of their usage
IceAngel89
It's the first post of this forum.
volksisme
You can see project real stuff here:
http://www.microsoft.com/sql/solutions/bi/projectreal.mspx