SSIS Progress

  • Hi,

    I would like to show the user the progress of SSIS packages in a c#-application (something like a progressbar)

    My approach is to implement an audit table containing entries for every process. Within the runtime of the process I would call a procedure which updates the status (10%, 20%, ..., 100%) of the corresponding audit-record. The c#-application would have to pull the status.

    Can I call a procedure with in a datapipe after let's say every 10000 rows? Can I achieve this without much loss of performance?

    Can SSIS-Events help me? If so, could you describe this?

    Thank you,

    Tobias

  • You seem to be new to programming with SSIS, so I would suggest you first go look at the books online information regarding using the SSIS object model to run packages:

    http://msdn.microsoft.com/en-us/library/ms136090.aspx

    If you have to use some kind of logging table (I do not really understand why, but we will go with the idea), the easiest solution would be to turn on the default logging and log the information events. This will give you some useful progress information without you having to program anything that is going to make your package horribly slow.

  • Thank you. I understand, that I can catch the (OnProgress?) Events which will show me the status?

    How would I get my Information (10%, 20%, .., 100%)?

  • SSIS does not capture percentage information on it's own. It can log how many records have been transferred, but it does not check for totals first, so there is not really a percentage. In addition, since it can multi-thread, the number of transferred records may not represent a percentage of time (there could be a single command object in the control flow that takes 99% of the time for the package to run).

    So I guess I am saying that there will not be anything generic that will be very accurate.

    I would not use a progress bar. You may have noticed that MS has gone toward a single animated gif for eye candy. My suggestion would be to have some kind of appropriate animated gif (a spinning wheel?) and then using the SSIS objects, report the currently running components and the latest message reported. Or, just go with a simple animated gif and save yourself a lot of work.

  • Thank you for that information. Actually a progress bar seems not to be the proper method. Perhaps we can use a animated gif..

  • Don't underestimate the value of simple eye-candy.

    I once had a bunch of users complaining about the time it took an ETL process to run (it was about 3 minutes). After coming to the conclusion that the ETL process was pretty well optimized, I took the eye-candy approach.

    So, I found a free animated gif of a puppy running around. I added a DIV tag and some java script (the package ran from a web page) and re-deployed.

    The users were amazed at how much faster it ran and I received a lot of compliments.

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

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