I want to analyse the performance time of each Data Flow task in an SSIS package to see how long each task takes. What is the best way of doing this? I assume each task could be a few milli seconds or less.PG

Have you tried implementing logging? http://msdn.microsoft.com/en-us/library/ms138020%28SQL.90%29.aspx

while developing the package in BIDS, There is a tab near to "control flow" tab named as "Progress" while you are running the package and "Execution Results" after execution of package . There you can check the process time to check the performance time. Let us TRY this | Don’t forget to mark the post(s) that answered your question http://quest4gen.blogspot.com/

Yes but logging seems to show the time for whole package started and ended. What I want is to get the start and end times of each task in the package. I have also looked at the progress tab and it does not give sufficient granularityPG

Yes but logging seems to show the time for whole package started and ended. What I want is to get the start and end times of each task in the package. I have also looked at the progress tab and it does not give sufficient granularity PG Logging can show time for individual tasks as well as the whole package. You can configure it to log for specific events and control what information is logged. Having said that, I think the progress tab is your best bet though I'm confused about it not containing enough information. Each task is listed along with start time, progress information, finish time and time elapsed. Could you explain exactly what information you need that isn't provided?

Under the progress tab, the start and end date/time is recorded for the Control Flow Tasks. I wanted the times for each Data Flow Transformation Task which is not shown under Progress Tab or the Log as far as I can tell. If this can be configured, how do you do it? Should I use script tasks to force entries into a log file of some kind?========================================I have resolved the perfomance issue. If data is loaded and processed from a single file, 8 million rows can be processed in about 5-10 minutes. If the same 8 million rows are in 6000 separate files, then SSIS is very, very, very slow and it would have taken about 50 hours to load. As far as I can tell, if SSIS only renamed and copied 6000 X 200KB files, that alone would take about 12 hours which is a big overhead. Even taking that into account, the Lookups, Updates and Inserts would have taken another 38 hours using the fast table load and without any Cartesian products on the Lookups....so it looks if the data is processed in say 6000 X 200 record chunks, SSIS is much, much slower even using Fast Table Load etc... PG

your package needs some tuning tips, please check your another post. your data is huge and you are using no. of heavy transformation (for memory) in the package. Divide your 1 DFT to 2 or more. Let us TRY this | Dont forget to mark the post(s) that answered your question http://quest4gen.blogspot.com/

HI ETL i need to store above mentined task realted info like task name, execution time in to a table can i achieve this ?

Implementing auditing consist many items in the check list. - Log performance detail for package and task - Log row count - Log errors/warnings - Log variables - Log connections You have 3 options when you want to log performance of each task. 1. Create your own framework using EventHandlers (OnPreExecute, OnPostExecute, OnError) Pros: Very flexible becuase you can log whatever you want in custom Log Table Cons: Very time consuming and hard to maintain. You have to write your own reports. 2: Enable Native Logging and write wrapper views/stored procs to analyze start/end time for Package/Tasks Pros: Very easy to setup Cons: You have very limited control how and where you want to log. All goes to Syslog table and from there you have to mine the information, write reports etc. 3: Use 3rd party SSIS Auditing and Monitoring Solution Pros: Very simple to implement auditing strategy. Canned reports. Realtime monitoring. Cons: Price tag and customization BI xPress is the only product right now in the market for last solution. Here is more info for auditing and logging capability of BI xPress SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document Entire BI | SSIS Tasks | BIDS Plugin