Handling metadata processes for SSIS projects
What is the best way to handle metadata processes for an entire project in SQL Server Integration Services (SSIS)? For example, how would I capture basic info, such as user name, row counts, start time, end time, server name, etc.
I'm particularly interested in row counts for data transformation at the intermediate level. Is there an example you could provide?
It turns out the SSIS defines system package variables you have read-only access to that contain the exact data you are looking for. In fact, Donald Farmer (Microsoft SSIS Guru) created a DTS package that appends this data to an audit table. You can view all of this information in his article 'Counting Rows in SQL Server Integration Services'
As you will see, some of the data that can be captured includes: package name, machine name, username, ErrorRows, GoodRows and Execution start time. Take a look in SSIS and view all of the System Defined Package variables.
This was first published in May 2006