Solved

How can I track the Processing times for my ETL/SSIS packages?

Posted on 2014-01-29
3
314 Views
Last Modified: 2016-02-10
I'm using SQL Server 2012, what is the best way to analyze the different processing times for my different ETL/SSIS packages?

I'm interested in seeing perhaps a comparison of PROCESSING times campared against each other for a nightly ETL Jobs Run..

Thanks
0
Comment
Question by:MIKE
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39819003
Most developers I know, pre-2012, created their own 'logging' table for SSIS package runs, with an id identity columns, and columns for the name, any variables passed, whatever.

When a package starts, an SQL Task in the beginning writes a row to this table, returns the id value, and then stores that value in an SSIS variable.

When a package ends, a SQL Task at the end updates that row based on the id = @id, and adding whatever other values you wish to track such as number of files processed, number of rows inserted-updated-deleted, etc.

Then, just query the table to view all history.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39819036
I was wondering if there was some kind of module or app in SQL Server 2012 that would help in this regard. I wonder if some SYS table automatically tracks the actual processing time spent in seconds or whatever,...?
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 39819047
Built-in Reporting shows some promise, but I haven't worked with it so can't give you a definitive answer.

I'll step back to encourage other experts to respond.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 25
SQL Query Task 11 43
Sql case statement to calculate totals 5 34
SQL question - help with insert for missing info 5 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question