SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Progress


SSIS Progress

Author
Message
tobe_ha
tobe_ha
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 384
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
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13221 Visits: 23078
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.
tobe_ha
tobe_ha
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 384
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%)?
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13221 Visits: 23078
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.
tobe_ha
tobe_ha
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 384
Thank you for that information. Actually a progress bar seems not to be the proper method. Perhaps we can use a animated gif..
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13221 Visits: 23078
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search