real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...