DTS Package progress

[robF]

I am currently writing a VB program that uses DTS to import data into a SQL Server 7 database. The import stuff is all fine & dandy, but i need to konw where at in the execution it is so i can update a progress bar. I know there is an OnProgress event for the dts.package object, but it isn't available in the option list that always pops up, i don't know if it isn't supported or what. can anyone help?

 

[99mel]

I'm looking to do the same thing.

Anyone know now?!?

 

[roverm]

If you are using ADO set a reference to the ADOX dll.
Then you should have all the events you need.

D'Mzzl!
RoverM

 

[softwarescience]

Hello

Not sure about the specifics but if you do the following

Private WithEvents dstPackageVariable As dts.Package

in the global declarations of a form
You will get all the events (inc OnProgress) available at design time for you to manipulate.


Hope this helps

 

[99mel]

The code was created through the DTS wizard in SQL server and so its in a Module. How can i still implement this
'Private WithEvents dstPackageVariable' so i can use the OnProgress procedure!?

I'm basically needing some feedback of how many rows it has imported, any error messages, whether the import was successful.

Cheers for any help!!!

 

[99mel]

Anyone know of any more information on this subject?

I've been looking at the WithEvents although can't get it to work.

Cheers!

 

[softwarescience]

I've been investigating and here it is!!
(Isn't Microsoft wonderfull!?!)

To do the WithEvents you have to do some faffing about but here goes.

Create a class module and paste the following

Option Explicit


Public WithEvents packageProgress As DTS.Package2

Private Sub packageProgress_OnError(ByVal EventSource As String, ByVal ErrorCode As Long, ByVal Source As String, ByVal Description As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal IDofInterfaceWithError As String, pbCancel As Boolean)
'
Debug.Print "An Error - " & ErrorCode

End Sub

Private Sub packageProgress_OnFinish(ByVal EventSource As String)
'
Debug.Print "Finishing - " & EventSource

End Sub


Private Sub packageProgress_OnProgress(ByVal EventSource As String, ByVal ProgressDescription As String, ByVal PercentComplete As Long, ByVal ProgressCountLow As Long, ByVal ProgressCountHigh As Long)

Debug.Print ProgressDescription & "     " & CStr(PercentComplete) & "     " & CStr(ProgressCountLow) & "     " & CStr(ProgressCountHigh)

End Sub

Private Sub packageProgress_OnQueryCancel(ByVal EventSource As String, pbCancel As Boolean)
'
Debug.Print "OnQueryCancel - " & EventSource & "             pbCancel = " & CStr(pbCancel)

'you will need a user interface with a global cancel variable if you wish the user to cancel the process
'otherwise you must set this as DTS will change this to true every second time it fires this event
pbCancel = False

End Sub


Private Sub packageProgress_OnStart(ByVal EventSource As String)
'
Debug.Print "Starting - " & EventSource

End Sub

In the bas file that DTS created put the following global declaration
Public goPackaProg As New Class1

The in the main procedure of the bas replace the line;
Set goPackage = goPackageOld
With
Set goPackaProg = New Class1
Set goPackaProg.packageProgress = goPackageOld

And change all references to the goPackage object with goPackaProg.packageProgress (only in the main proc though)
i.e.
Old = goPackage.Name = "New Package"
New = goPackaProg.packageProgress.Name = "New Package"


You can now use the class packageProgress_OnProgress event to display your progress.

Hope this helps

PS This only reports progress when importing data and you may need to modify the ProgressRowCount in your task proc to a lower number (this indicates when the progress event fires)

 

[99mel]

Thanks for your help!!!

Ive been investigating it all day too from MSDN on the net!
I've actually got it working (somehow) when i step through it, but not when i run it! i'm going to look at yours and see if it does what mines doing.

When i run it, it just seems to hang! i then have to end the task as it justs hangs! if i F8 into the procedure 'objPackage_OnQueryCancel' (i got the code from

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_46xv.asp)

it works fine if i just leave it (dnt step into the procedure) i get message boxs giving me the total rows entered. If i simply run the code it just hangs.


Does that make any sense?

Anyhoo ill try your code out. Thanks again!!

 

[softwarescience]

Yep, that was happening for a while.

I think you have to put at least a comment in each possible event for it. That seemed to allow it to continue.

The code I posted seems to work fine.

Let me know if it works as you expect.

 

[99mel]

Your code works fine when i step through it again!
When i run it (without stepping into the code), it just hangs and i have to end the task through windows!

I tryed adding DoEvents but to no avail.

Any ideas?

 

[99mel]

if i place a msgbox in each of the 'packageProgress' procedures in the class module it works fine!!!!!

Although if i just write the return values from these procedures into a text box on a form the program just hangs! doesn't import the data either.

 

[softwarescience]

Hello

What a nightmare!

I've retried the code (I've even compiled it) and it seems to work fine without hanging.

I'm not sure what to suggest
Another possibilty is to store the package as a 'Local Package' on SQL server (rather the saving it as a module). This can then be set to the DTS.Package variable in VB so you don't have to deal with other code and classes.
However, this may raise the same problem with stepping through (as effectively it is the same process).

By the way my dev platform is;
Windows 2000 SP2
VB6 Sp5
SQL Server 2000
Maybe this has an impact.

Sorry I can't help more

 

[99mel]

I have Windows NT 4
VB6
SQL Server 2000

so that shouldn't be a problem.

It works fine if i simply msgbox each parameter
oh well, i'm gonna have a look through all the properties of the 'goPackaProg.packageProgress' as something might need to change there maybe??

Anyhoo cheers for your help! its near enough working!!