Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Progress Expand / Collapse
Author
Message
Posted Monday, May 5, 2008 1:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 30, 2012 6:49 AM
Points: 94, Visits: 359
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
Post #495224
Posted Tuesday, May 6, 2008 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #495574
Posted Thursday, May 8, 2008 12:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 30, 2012 6:49 AM
Points: 94, Visits: 359
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%)?
Post #496880
Posted Thursday, May 8, 2008 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #497039
Posted Friday, May 9, 2008 1:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 30, 2012 6:49 AM
Points: 94, Visits: 359
Thank you for that information. Actually a progress bar seems not to be the proper method. Perhaps we can use a animated gif..
Post #498174
Posted Friday, May 9, 2008 1:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #498178
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse